create table #temp (Student_ID char(9),
				    Sex char(1),
				    RaceCode varchar(5),
				    Lunch int,
			        Student_ESL int,
				    GIEP int,
					IEP int,
				    SchoolCode int)

Insert into #temp values ('004242726','F','Black',1,0,0,0,204)
Insert into #temp values ('004242734','F','Black',0,1,0,0,356)
Insert into #temp values ('004242777','M','White',1,0,0,0,141)
Insert into #temp values ('004242793','F','Black',1,0,0,1,164)
Insert into #temp values ('004242831','M','White',0,0,0,1,141)
Insert into #temp values ('004242866','M','White',1,0,0,0,204)
Insert into #temp values ('004242882','M','White',0,1,0,0,141)
Insert into #temp values ('004242890','F','Black',0,0,0,0,204)
Insert into #temp values ('004242971','F','Black',0,0,0,1,164)
Insert into #temp values ('004243129','F','Black',0,0,0,1,204)
Insert into #temp values ('004243137','M','Black',0,0,0,0,164)
Insert into #temp values ('004243188','M','Black',1,0,0,0,204)
Insert into #temp values ('004243196','F','Other',1,1,0,0,204)
Insert into #temp values ('004243285','M','Other',0,0,0,1,204)
Insert into #temp values ('004243293','F','Other',0,0,0,1,164)
Insert into #temp values ('004243323','M','Other',0,0,0,1,204)
Insert into #temp values ('004243447','F','Black',1,1,0,0,204)
Insert into #temp values ('004243455','M','Black',0,0,0,0,164)

	  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

;WITH Unpivoted AS (
    SELECT RaceCode, Code, Value
    FROM #Temp
    CROSS APPLY (
        VALUES ('LUNCH', Lunch), ('ESL', Student_ESL), ('GIEP', GIEP), ('IEP', IEP)
        ) a (Code, Value)
    )
SELECT Category=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
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
GROUP BY Code

;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

;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

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;

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

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;

================================================================================
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.

OPTION (MAXDOP 2)