dwain.c (8/8/2012)

---

You can try something like this, although I don't get the same counts as you do:

;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

I count 9 males and 9 females in your data and my query above returns this.

Note that I've used the "Other UNPIVOT" because it is usually faster: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/