T-SQL Pivot with multiple aggregates on the same column
sql-server, t-sql
Solution
Yes.
Just use the old style `CASE` syntax.
SELECT AVG(CASE WHEN Year = 2001 THEN Score END) AS 2001_Avg,
STDEV(CASE WHEN Year = 2001 THEN Score END) AS 2001_StDev /*...*/
`PIVOT` is just (less versatile) syntactic sugar for this anyway.
Oracle supports multiple aggregates in `PIVOT` but TSQL doesn't.
Problem
This seems like it should be a common need, but I'm not seeing how to do this using T-SQL PIVOT function. Specifically, I want to calculate the AVG and STDEV for a column over a Pivot column value. The data look something like this (lots of data omitted, but this gets to the heart): ``` -------------------------------------------------------------------------- ID Year PersonID Score -------------------------------------------------------------------------- 106 2001 1 20 107 2002 1 30 108 2003 1 40 109 2004 1 50 106 2002 2 20 107 2003 2 50 108 2004 2 30 109 2005 2 10 -------------------------------------------------------------------------- ``` I would like to see the following output ``` -------------------------------------------------------------------------- NonPivotCol1 NonPivotCol2 2001_Mean 2001_Avg 2002_Mean 2002_Avg Etc -------------------------------------------------------------------------- Some Value Some Value 32 5.2 28 3.1 Etc. -------------------------------------------------------------------------- ``` Do I need to revert to the old CASE statement logic? Thanks!