Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replacing NULL with 0 when using the Pivot operator Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 11:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:58 AM
Points: 158, Visits: 689
Is there anyway to replace a NULL with 0 when using the PIVOT operator? Or is there some other way to write the query (perhaps not using PIVOT) or introduce some "fake" rows?

Here's an example:


CREATE TABLE #office_info_by_location_id
(
location_name VARCHAR(50) NULL,
month VARCHAR(7) NULL,
total_offices INT NULL
);

INSERT INTO #office_info_by_location_id
VALUES ('a', '2008-03', 3);
INSERT INTO #office_info_by_location_id
VALUES ('a', '2008-04', 4);
INSERT INTO #office_info_by_location_id
VALUES ('b', '2008-03', 5);


SELECT *
FROM #office_info_by_location_id
PIVOT (MAX(total_offices) FOR month IN ([2008-03],[2008-04])) AS temp;

Current Results:
location_name 2008-03 2008-04
a 3 4
b 5 NULL

Desired Results:
location_name 2008-03 2008-04
a 3 4
b 5 0
Post #519288
Posted Wednesday, June 18, 2008 11:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 7,084, Visits: 14,685
I usually end up handling that in the outer select. Meaning - once the PIVOT has been done.

Something like:

select locationname,
isnull([2008-03],0) as [2008-03],
etc...

You certainly could do it with some kind of a UNION ALL, assuming the extra rows don't somehow mess up the aggregation.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #519299
Posted Wednesday, June 18, 2008 11:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Well, you can do something like this:

;with CTE as 
(SELECT *
FROM #office_info_by_location_id
PIVOT (MAX(total_offices) FOR month IN ([2008-03],[2008-04])) AS temp)
select location_name,
isnull([2008-03], 0) as [2008-03],
isnull([2008-04], 0) as [2008-04]
from cte



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #519305
Posted Wednesday, June 18, 2008 12:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
Matt Miller (6/18/2008)
I usually end up handling that in the outer select. Meaning - once the PIVOT has been done.

Something like:

select locationname,
isnull([2008-03],0) as [2008-03,
etc...

You certainly could do it with some kind of a UNION ALL, assuming the extra rows don't somehow mess up the aggregation.

I also handle this in the outer query. You can use isnull or coalesce to substitute a 0 for null values.




My blog: http://jahaines.blogspot.com
Post #519346
Posted Saturday, October 05, 2013 4:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:40 AM
Points: 60, Visits: 257
You could take a more dynamic approach and cross join your locations with your months to get all possible combinations of location / month, and then left join your original table to the results - you could then use the ISNULL function in the pivot source to deal with NULL values - for example:

CREATE TABLE #office_info_by_location_id
(
location_name VARCHAR(50) NULL,
month VARCHAR(7) NULL,
total_offices INT NULL
);

INSERT INTO #office_info_by_location_id
VALUES ('a', '2008-03', 3);
INSERT INTO #office_info_by_location_id
VALUES ('a', '2008-04', 4);
INSERT INTO #office_info_by_location_id
VALUES ('b', '2008-03', 5);



SELECT x.[month],x.location_name,ISNULL(y.total_offices,0) AS 'total_offices'
INTO #PivotSource
FROM
(
SELECT DISTINCT a.[month],b.location_name
from #office_info_by_location_id a
CROSS JOIN
#office_info_by_location_id b
) x
LEFT JOIN
(
SELECT location_name,[month],total_offices
FROM #office_info_by_location_id
) y ON x.location_name = y.location_name and x.[month] = y.[month]



--now pivot the results using dynamic SQL

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.month
FROM #PivotSource AS t2
ORDER BY '],[' + t2.month
FOR XML PATH('')
), 1, 2, '') + ']'

SELECT @query =

'SELECT *
FROM
#PivotSource AS t
PIVOT
(
MAX(total_offices)
FOR [month] IN( ' + @cols + ' )' +
' ) AS p ; ';

EXECUTE(@query);
Post #1501875
Posted Saturday, October 05, 2013 6:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 2,763, Visits: 5,911
Maybe you could try a CROSS-TABS approach.

SELECT location_name,
MAX( CASE WHEN month = '2008-03' THEN total_offices ELSE 0 END) [2008-03],
MAX( CASE WHEN month = '2008-04' THEN total_offices ELSE 0 END) [2008-04]
FROM #office_info_by_location_id
GROUP BY location_name

More on this method and the possibility to make it dynamic on the following links:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501880
Posted Saturday, October 05, 2013 6:34 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
Please note: 5 year old thread


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse