Single or Partnered - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Single or Partnered

Author

Topic

wdarnellg
Starting Member

14 Posts

Posted - 2011-12-05 : 14:53:30

I have a need to alter a view so that it displays the result set as data in rows. i.e.
FamilyType(Column Name) FamilyNumber(ColumnName)
SingleFamily 125
Partnered Family 127

I am getting the data from an existing table as shown in the code below where I have tried to figure out this dilema with my limited knowledge. Help is appreciated

ALTER VIEW [dbo].[vw_sj_TotalSingleOrPartneredFamily]
FamilyTypes nvarchar(50)

AS

SET FamilyTypes = (SUM(CASE WHEN P.OtherParentID > 0 THEN 1 ELSE 0 END) AS PartneredFamily, SUM(CASE WHEN P.OtherParentID IS NULL OR
p.OtherParentID = 0 THEN 1 ELSE 0 END) AS SingleFamily), ISNULL(COUNT(p.LastName), 0)[TotalFamilies]
FROM dbo.sj_Players AS p INNER JOIN
dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN
dbo.sj_ParentGuardian AS opg ON p.OtherParentID = opg.ParentID

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-12-05 : 15:00:19

Try SELECT instead of SET

Nathan Skerl

wdarnellg
Starting Member

14 Posts

Posted - 2011-12-05 : 15:28:49

No Go... something screwy with the syntax.
It doesn't like the FamilyTypes declaration, says 'Expecting (,AS, or With' and similar errors on the Case and Count functions.


FamilyTypes nvarchar(50)

AS
 

SELECT     FamilyTypes = (SUM(CASE WHEN P.OtherParentID > 0 THEN 1 ELSE 0 END) AS PartneredFamily, SUM(CASE WHEN P.OtherParentID IS NULL OR
                      p.OtherParentID = 0 THEN 1 ELSE 0 END)) AS SingleFamily, ISNULL(COUNT(p.LastName), 0)[TotalFamilies]
FROM         dbo.sj_Players AS p INNER JOIN
                      dbo.sj_ParentGuardian AS pg ON p.ParentID = pg.ParentID LEFT OUTER JOIN
                      dbo.sj_ParentGuardian AS opg ON p.OtherParentID = opg.ParentID

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-12-05 : 15:51:55

ok, not sure what youre trying to do with FamilyTypes under your view declaration.

You cannot have input parameters within a view.

This is at least valid syntax, not positive it will return your desired result:


ALTER VIEW [dbo].[vw_sj_TotalSingleOrPartneredFamily]
AS
SELECT	SUM(CASE WHEN i > 0 THEN 1 ELSE 0 END) AS [PartneredFamily], 
	SUM(CASE WHEN P.OtherParentID IS NULL OR p.OtherParentID = 0 THEN 1 ELSE 0 END) AS [SingleFamily], 
	ISNULL(COUNT(p.LastName), 0) AS [TotalFamilies]
FROM	dbo.sj_Players AS p 
INNER 
JOIN	dbo.sj_ParentGuardian AS pg ON 
	p.ParentID = pg.ParentID 
LEFT 
OUTER 
JOIN	dbo.sj_ParentGuardian AS opg ON 
	p.OtherParentID = opg.ParentID

wdarnellg
Starting Member

14 Posts

Posted - 2011-12-05 : 19:04:37

I am trying to collect data for a pie chart control in a desktop app. I need a column for the x axis (rows) and the y axis(columns). The sql view that I have and you duplicated only returns columns. I need the results in rows. I am hoping to find out if that is even possible, and to be shown how if it is.

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-12-06 : 00:05:24

Ok, so right now you have:


PartneredFamily    SingleFamily    TotalFamilies
---------------    ------------    -------------
1                  2               3

Do you want this instead:


FamilyType    Count
----------    -----
Partnered     1
Single        2
Total         3

Nathan Skerl

wdarnellg
Starting Member

14 Posts

Posted - 2011-12-06 : 00:21:31

Yes, that is correct. :o)

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-12-06 : 00:51:27

If you post the DDL for all the tables involved we can probably further simplify this query, but the basic idea is:


;with c_Families (PartneredCount, SingleCount, TotalCount)
as	(	SELECT	SUM(CASE WHEN i > 0 THEN 1 ELSE 0 END)
			SUM(CASE WHEN P.OtherParentID IS NULL OR p.OtherParentID = 0 THEN 1 ELSE 0 END),
			ISNULL(COUNT(p.LastName), 0)
		FROM	dbo.sj_Players AS p 
		INNER 
		JOIN	dbo.sj_ParentGuardian AS pg ON 
			p.ParentID = pg.ParentID 
		LEFT 
		OUTER 
		JOIN	dbo.sj_ParentGuardian AS opg ON 
			p.OtherParentID = opg.ParentID
	)
select 'Partnered' as [FamilyType],
	PartneredCount
from	c_Families
union all
select	'Single',
	SingleCount
from	c_Families
union all
select	'Total',
	TotalCount
from	c_Families

Take my example and reduce it do the essential queries. I only have your query to use as example; you can probably clean it up.


select    'Total' as [FamilyType],
          count(*)
from      dbo.YourTable
union all
select    'SomeType',
          sum(case when sot.someColumn = 1 then 1 else 0 end)
from      dbo.YourTable yt
join      dbo.SomeOtherTable sot on
          yt.Pk = sot.Pk
union all
select    'Yakaroni',
          99

you get the idea.

SQL has PIVOT and UNPIVOT operators that do the same operations with a more elegant syntax.

[url]http://msdn.microsoft.com/en-us/library/ms177410.aspx[/url]

wdarnellg
Starting Member

14 Posts

Posted - 2011-12-06 : 11:01:35

Thank you for all of your help. I found a solution that works perfectly. I don't know if there is a knowledge base archive, but I will post the code anyway.
Thanks again!

CREATE VIEW SomeView AS
SELECT ISNULL(FamilyStatus, 'TotalFamilies') AS FamilyStatus, COUNT(*) AS FamilyCount 
FROM (
	SELECT CASE WHEN OtherParentID > 0 THEN 'PartneredFamily' ELSE 'SingleFamily' END AS FamilyStatus 
	FROM dbo.sj_Players) AS f 
GROUP BY FamilyStatus WITH ROLLUP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources