August 13, 2014 at 7:59 am
I'm trying to use 'ISNULL' to get rid of the NULLS in my results for this cube/pivot query. I've tried
'PIVOT ((ISNULL(SUM(Sales),0) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p' but I get errors. Here is my DDL & DML I'm using and the results with the NULLS I'm getting... Thanks for any help!
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
SELECT EmpId, [2005], [2006], [2007], [ALL]
FROM
(
SELECT
CASE WHEN GROUPING(EmpId) = 0
THEN CAST (EmpId AS CHAR(7))
ELSE 'ALL'
END AS EmpId,
CASE WHEN GROUPING(Yr) = 0
THEN CAST (Yr AS CHAR(7))
ELSE 'ALL'
END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p
EMPID 2005 2006 2007 ALL
1 12000 18000 25000 55000
2 15000 6000 (null) 21000
3 (null) 20000 24000 44000
ALL 27000 44000 49000 120000
August 13, 2014 at 8:04 am
Need to put the ISNULL around your select
SELECT EmpId, ISNULL([2005],0), ISNULL([2006],0), ISNULL([2007],0), ISNULL([ALL],0)
FROM
(
SELECT
...
...
August 13, 2014 at 8:08 am
ISNULL needs to be part of the SELECT, not the PIVOT.
Modified code:
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
SELECT EmpId, ISNULL([2005],0.) AS [2005], ISNULL([2006], 0.) AS [2006],
ISNULL([2007], 0.) AS [2007], [ALL]
FROM
(
SELECT
CASE WHEN GROUPING(EmpId) = 0 THEN CAST (EmpId AS CHAR(7)) ELSE 'ALL' END AS EmpId,
CASE WHEN GROUPING(Yr) = 0 THEN CAST (Yr AS CHAR(7)) ELSE 'ALL' END AS Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p
Results:
EmpId 2005 2006 2007 ALL
------- --------------------- --------------------- --------------------- ---------------------
1 12000.00 18000.00 25000.00 55000.00
2 15000.00 6000.00 0.00 21000.00
3 0.00 20000.00 24000.00 44000.00
ALL 27000.00 44000.00 49000.00 120000.00
(4 row(s) affected)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2014 at 8:32 am
This is another option that might perform better considering that it reads the table once instead of twice. I can't assure that but it could be worth the try.
SELECT ISNULL(CAST (EmpId AS CHAR(7)), 'ALL') EmpId,
SUM(CASE WHEN Yr = 2005 THEN Sales ELSE 0 END) [2005],
SUM(CASE WHEN Yr = 2006 THEN Sales ELSE 0 END) [2006],
SUM(CASE WHEN Yr = 2007 THEN Sales ELSE 0 END) [2007],
SUM(Sales) [ALL]
FROM (
SELECT EmpId,
Yr,
SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr
)s
GROUP BY EmpId WITH CUBE
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply