Dumb Question... Pivot - 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 2005 Forums

Transact-SQL (2005)

Dumb Question... Pivot

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-03 : 15:41:55

I have the following query


SELECT *
FROM 
(
	Select * 
	from
	#GroupedInsurance
	Pivot
(
	Sum(Rate)
	For Coverage
	in (Single, [Single + 1], Family, Composite)) as p
) a
PIVOT
 (
 Count(InsuranceTypeDesc)
 FOR InsuranceTypeDesc
 IN (Medical,Life, Dental, Vision, [Short Term Disability])
 )
 AS p

Results


EmployerName                                       Insurance                                                                                             Single                Single + 1            Family                Composite             Medical     Life        Dental      Vision      Short Term Disability
-------------------------------------------------- ----------------------------------------------------------------------------------------------------- --------------------- --------------------- --------------------- --------------------- ----------- ----------- ----------- ----------- ---------------------
#8028 Enterprises Inc.                             NPWU - Insurance HMO                                                                                  461.00                NULL                  NULL                  NULL                  1           1           1           1           1
324 BUILDING FUND                                  NPWU - Insurance PPO                                                                                  371.00                NULL                  NULL                  NULL                  1           1           1           1           0
AAA SNOWBUSTERS INC                                NPWU - Insurance HMO                                                                                  392.00                NULL                  NULL                  NULL                  1           1           1           1           1
AASBA                                              NPWU - Insurance HMO                                                                                  330.00                647.00                NULL                  NULL                  1           0           0           0           0
Access Community Health Network                    NPWU - Insurance HMO                                                                                  NULL                  NULL                  NULL                  37.00                 1           1           1           1           0
ADAMO, INC.                                        NPWU - Insurance PPO                                                                                  625.00                NULL                  NULL                  NULL                  1           1           1           1           0

how do I elimate the null and use a 0 instead? Coalesce, Isnull, and a case statement all do not seem to work for me..

I know this is probably a simple fix but I've wasted a good 20 minutes on it thus far!

Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-03 : 16:09:21

RESOLVED

It's the exact same as any other query... Don't know why I thought it needed to be in the Pivot syntax.


SELECT EmployerName,Insurance,coalesce(Single,0) as Single, Coalesce([Single + 1],0) as [Single + 1], Coalesce(Family,0) as Family, Coalesce(Composite,0) as Composite,Medical,Life,Dental,Vision,[Short Term Disability]
FROM 
(
	Select * 
	from
	#GroupedInsurance
	Pivot
(
	Sum(Rate)
	For Coverage
	in (Single, [Single + 1], Family, Composite)) as p
) a
PIVOT
 (
 Count(InsuranceTypeDesc)
 FOR InsuranceTypeDesc
 IN (Medical,Life, Dental, Vision, [Short Term Disability])
 )
 AS p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources