Pivoting Columns into Rows and Rows into Columns at the same time. (SSMS)

Question

I have the following table...let's call it Table1

 TERM           PERIOD  Average     StandardDeviation   Minimum
+ 1 Period      2012Q3  0.00417     0.00722             0
+ 120 Periods   2012Q3  0.02951     0.0014              0.028615
+ 20 Periods    2012Q3  0.009898    0.0037              0.007612
+ 40 Periods    2012Q3  0.018255    0.00262             0.016565
+ 1 Period      2012Q4  0.005       0.007               0
+ 120 Periods   2012Q4  0.026       0.001               0.03
+ 20 Periods    2012Q4  0.007       0.003               0.0042
+ 40 Periods    2012Q4  0.018       0.002               0.0165

Is there a way to code this so I can have a table that looks like so:

TERM           PATHS              2012Q3    2012Q4
+ 1 Period     Average            0.00417   0.005
+ 1 Period     StandardDeviation  0.00722   0.007
+ 1 Period     Minimum            0         0

...and so on for each TERM ( + 120 Periods, + 40 Periods, etc).

Taryn · Accepted Answer · 2013-02-01T20:42:01.867

To produce this result, you will want to use both the UNPIVOT and the PIVOT function. The UNPIVOT takes the columns Average, StandardDeviation and Minimum and converts them into multiple rows. Once you have the rows, then you can apply the PIVOT to the Period values. Your code will be similar to this:

select term, paths,[2012Q3], [2012Q4]
from
(
  select term, period, paths, value
  from
  (
    select term, period, Average, StandardDeviation, Minimum
    from table1
  ) s
  unpivot
  (
    value
    for paths in (Average, StandardDeviation, Minimum)
  ) unpiv
) src
pivot
(
  max(value)
  for period in ([2012Q3], [2012Q4])
) piv

See SQL Fiddle with Demo.

This can also be done using a UNION ALL to unpivot the data and an aggregate function with a CASE expression to pivot:

select term,
  paths,
  max(case when period = '2012Q3' then value end) [2012Q3],
  max(case when period = '2012Q4' then value end) [2012Q4]
from
(
  select term, period, 'Average' Paths, average value
  from Table1
  union all
  select term, period, 'StandardDeviation' Paths, StandardDeviation
  from Table1
  union all
  select term, period, 'Minimum' Paths, Minimum
  from Table1
) src
group by term,  paths

See SQL Fiddle with Demo.

The above versions work great if you have a known number of period values. If you don't then you will want to use dynamic sql to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(PERIOD) 
                    from Table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
    = 'select term, paths, '+@cols+'
       from
       (
         select term, period, paths, value
         from
         (
           select term, period, Average, StandardDeviation, Minimum
           from table1
         ) s
         unpivot
         (
           value
           for paths in (Average, StandardDeviation, Minimum)
         ) unpiv
       ) p
       pivot
       (
          max(value)
          for period in('+@cols+')
       ) piv'

execute(@query)

See SQL Fiddle with Demo.

All of the versions produce the same result.

Yeah I always try to use Dynamic SQL when I have a whole ton of columns — Kristina, Feb 01 '13 at 21:01

score 0 · Answer 2 · answered Feb 01 '13 at 20:37

I approach this as an aggregation and a join . . .

select term, paths,
       MIN(case when period = '2012Q3'
                then (case when which = 'Average' then t.average
                           when which = 'StandardDeviation' then t.StandardDeviation
                           when which = 'Minimum' then t.Minimum
                      end)
            end) as "2012Q3",
       MIN(case when period = '2012Q4' 
                then (case when which = 'Average' then t.average
                           when which = 'StandardDeviation' then t.StandardDeviation
                           when which = 'Minimum' then t.Minimum
                      end)
            end) as "2012Q4"
from Table1 t cross join
     (select 'Average' as paths union all
      select 'StandardDeviation' union all
      select 'Minimum'
     ) const
group by term, paths

Pivoting Columns into Rows and Rows into Columns at the same time. (SSMS)

2 Answers2