T-SQL Pivot with multiple aggregates on the same column

Question

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!

possible duplicate of [SQL Server PIVOT - Multiple Aggregates](http://stackoverflow.com/questions/1243567/sql-server-pivot-multiple-aggregates) — JeffO, Aug 12 '13 at 01:44

score 4 · Accepted Answer · edited May 23 '17 at 11:50

4

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.

edited May 23 '17 at 11:50

Community

1
1

answered Aug 11 '13 at 20:08

Martin Smith

438,706
87
741
845

score 2 · Answer 2 · answered Aug 11 '13 at 20:13

use case statements. You have to specify all columns anyway and I'd say that case is more usable than pivot

select
  avg(case when [Year] = 2001 then [Score] else null end) as [2001_Avg],
  avg(case when [Year] = 2002 then [Score] else null end) as [2002_Avg]
from Table1

Giannis Paraskevopoulos · Answer 3 · 2013-08-11T20:19:16.950

0

You have to make two PIVOTs and join them

WITH MinPivot AS
(
  SELECT 
         [2001] AS [2001_MEAN], 
         [2002] AS [2002_MEAN], 
         [2003] AS [2003_MEAN], 
         [2004] AS [2004_MEAN], 
         [2005] AS [2005_MEAN]
  FROM
  (
    SELECT Year,Score
    FROM Table1
  ) As S
  PIVOT
  (
     STDEV(Score)
     FOR Year in([2001],[2002],[2003],[2004],[2005])
  )
  AS pvt
),
AvgPivot AS
(
  SELECT 
         [2001] AS [2001_AVG],
         [2002] AS [2002_AVG],
         [2003] AS [2003_AVG], 
         [2004] AS [2004_AVG], 
         [2005] AS [2005_AVG] 
  FROM
  (
    SELECT Year,Score
    FROM Table1
  ) As S
  PIVOT
  (
     AVG(Score)
     FOR Year in([2001],[2002],[2003],[2004],[2005])
  )
  AS pvt
)

SELECT *
FROM MinPivot M
CROSS JOIN AvgPivot A

Have a look at this fiddle example

edited Aug 11 '13 at 20:19

answered Aug 11 '13 at 20:06

Giannis Paraskevopoulos

18,261
1
49
69

This is much less efficient than just using `CASE` as you perform the grouping and aggregation twice (as well as adding an additional join in) – Martin Smith Aug 11 '13 at 20:23
It is an alternative in any case. – Giannis Paraskevopoulos Aug 11 '13 at 20:25

T-SQL Pivot with multiple aggregates on the same column

3 Answers3