Performance test as promised: -
SET NOCOUNT ON;
SELECT TOP 1000000 Student_ID,
Sex = CASE WHEN sex = 1 THEN 'M' ELSE 'F' END,
RaceCode = CASE race WHEN 0 THEN 'Other' WHEN 1 THEN 'Black' ELSE 'White' END,
Lunch = lunch, Student_ESL = esl, GIEP = giep, IEP = iep,
SchoolCode = schoolcode
INTO #temp
FROM master.dbo.syscolumns sc1
CROSS JOIN master.dbo.syscolumns sc2
CROSS JOIN master.dbo.syscolumns sc3
CROSS APPLY (SELECT DISTINCT LEFT('0' + (CAST((ABS(CHECKSUM(NEWID())) % 999999999) + 1 AS VARCHAR(9))),9)) sc4(Student_ID)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc5(sex)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 3)) sc6(race)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc7(lunch)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc8(esl)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc9(giep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 2)) sc10(iep)
CROSS APPLY (SELECT (ABS(CHECKSUM(NEWID())) % 899) + 100) sc11(schoolcode);
CREATE CLUSTERED INDEX PK_Student_ID_temp ON #temp (Student_ID);
CREATE NONCLUSTERED INDEX NC_SchoolCode_temp ON #temp (SchoolCode);
PRINT REPLICATE('=',80);
PRINT 'DWAIN';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
;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
SET STATISTICS IO, TIME OFF;
PRINT REPLICATE('=',80);
PRINT 'CADAVRE';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
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;
SET STATISTICS IO, TIME OFF;
Results: -
================================================================================
DWAIN
================================================================================
Sex BLACK WHITE OTHER
------ ----------- ----------- -----------
FEMALE 197 177 171
MALE 164 199 191
LUNCH 178 177 178
GIEP 193 187 179
IEP 184 189 188
ESL 180 186 178
Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 2, logical reads 6762, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 20 ms.
================================================================================
CADAVRE
================================================================================
Code BLACK WHITE OTHER
------ ----------- ----------- -----------
Male 164 199 191
Female 197 177 171
IEP 184 189 188
GIEP 193 187 179
LUNCH 178 177 178
ESL 180 186 178
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp'. Scan count 1, logical reads 3381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 16 ms.
I'm a bit surprised, seems the results are comparable. So I guess it's down to personal preference.