June 18, 2008 at 11:17 am
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_name2008-032008-04
a34
b5NULL
Desired Results:
location_name2008-032008-04
a34
b50
June 18, 2008 at 11:39 am
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?
June 18, 2008 at 11:46 am
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
June 18, 2008 at 12:52 pm
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.
October 5, 2013 at 4:49 pm
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);
October 5, 2013 at 6:06 pm
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:
October 5, 2013 at 6:34 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply