dwain.c (8/8/2012)
Thanks Lynn! Apparently if I could read, I would've come up with this version instead:
;WITH Unpivoted AS (
SELECT SchoolCode, RaceCode, Code, Value
FROM #Temp
CROSS APPLY (
VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
) a (Code, Value)
)
SELECT Sex=CASE Sex WHEN 'M' THEN 'MALE' ELSE 'FEMALE' END
,BLACK=COUNT(CASE WHEN RaceCode = 'Black' THEN 1 END)
,WHITE=COUNT(CASE WHEN RaceCode = 'White' THEN 1 END)
,OTHER=COUNT(CASE WHEN RaceCode = 'Other' THEN 1 END)
FROM #Temp
WHERE SchoolCode = 204
GROUP BY Sex
UNION ALL
SELECT Code
,BLACK=SUM(CASE WHEN RaceCode = 'Black' THEN Value ELSE 0 END)
,WHITE=SUM(CASE WHEN RaceCode = 'White' THEN Value ELSE 0 END)
,OTHER=SUM(CASE WHEN RaceCode = 'Other' THEN Value ELSE 0 END)
FROM Unpivoted
WHERE SchoolCode = 204
GROUP BY Code
Do you not like GROUP BY ROLLUP ?
e.g.
SELECT Code, BLACK, WHITE, OTHER
FROM (SELECT Code,
BLACK=SUM(CASE WHEN RaceCode = 'Black' AND Value = 1 THEN 1 ELSE 0 END),
WHITE=SUM(CASE WHEN RaceCode = 'White' AND Value = 1 THEN 1 ELSE 0 END),
OTHER=SUM(CASE WHEN RaceCode = 'Other' AND Value = 1 THEN 1 ELSE 0 END),
MAX(Pos) AS Pos
FROM (SELECT SchoolCode, RaceCode, Code, Value, Pos
FROM #temp
CROSS APPLY (VALUES ('LUNCH', Lunch, 5), ('ESL', Student_ESL, 6), ('GIEP', GIEP, 4), ('IEP', IEP, 3),
('Male', CASE WHEN Sex = 'M' THEN 1 ELSE 0 END, 1),
('Female', CASE WHEN Sex = 'F'THEN 1 ELSE 0 END, 2)
) a(Code, Value, Pos)) a
WHERE SchoolCode = 204
GROUP BY ROLLUP (Code)
) a
WHERE Code IS NOT NULL
ORDER BY Pos;
Results in: -
Code BLACK WHITE OTHER
------ ----------- ----------- -----------
Male 1 1 2
Female 4 0 1
IEP 1 0 2
GIEP 0 0 0
LUNCH 3 1 1
ESL 1 0 1
I've only added the ORDER BY to replicate the ordering that the OP posted.
I'll set up a big performance test in a little while to test the difference, but my gut feeling is that the GROUP BY ROLLUP will be faster.