Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacing NULL with 0 when using the Pivot operator


Replacing NULL with 0 when using the Pivot operator

Author
Message
jlp3630
jlp3630
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 704
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18082
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?
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
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
Wolfmeister
Wolfmeister
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 342
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);
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8548 Visits: 18136
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44389
Please note: 5 year old thread


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search