SQL Server : dynamic pivot over 5 columns

Question

I'm having a very tough time trying to figure out how to do a dynamic pivot in SQL Server 2008 with multiple columns.

My sample table is as follows:

ID  YEAR  TYPE  TOTAL   VOLUME
DD1 2008    A   1000    10
DD1 2008    B   2000    20
DD1 2008    C   3000    30
DD1 2009    A   4000    40
DD1 2009    B   5000    50
DD1 2009    C   6000    60
DD2 2008    A   7000    70
DD2 2008    B   8000    80
DD2 2008    C   9000    90
DD2 2009    A   10000   100
DD2 2009    B   11000   110
DD2 2009    C   12000   120

and I'm trying the pivot it as follows:

ID  2008_A_TOTAL    2008_A_VOLUME   2008_B_TOTAL    2008_B_VOLUME   2008_C_TOTAL    2008_C_VOLUME   2009_A_TOTAL    2009_A_VOLUME   2009_B_TOTAL    2009_B_VOLUME   2009_C_TOTAL    2009_C_VOLUME
DD1 1000            10              2000            20              3000            30              4000            40              5000            50              6000            60
DD2 7000            70              8000            80              9000            90              10000           100             11000           110             12000           120

My SQL Server 2008 query is as follows to create the table:

CREATE TABLE ATM_TRANSACTIONS 
(
 ID varchar(5),
 T_YEAR varchar(4),
 T_TYPE varchar(3), 
 TOTAL int,
 VOLUME int
);

INSERT INTO ATM_TRANSACTIONS
(ID,T_YEAR,T_TYPE,TOTAL,VOLUME)

VALUES
('DD1','2008','A',1000,10),
('DD1','2008','B',2000,20),
('DD1','2008','C',3000,30),
('DD1','2009','A',4000,40),
('DD1','2009','B',5000,50),
('DD1','2009','C',6000,60),
('DD2','2008','A',7000,70),
('DD2','2008','B',8000,80),
('DD2','2008','C',9000,90),
('DD2','2009','A',10000,100),
('DD2','2009','B',11000,110),
('DD2','2009','C',1200,120);

The T_Year column may change in the future but the T_TYPE column is generally know, so I'm not sure if I can use a combination of the PIVOT function in SQL Server with dynamic code?

I tried following the example here:

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

but I ended up with with weird results.

Answer 1

In order to get the result, you will need to look at unpivoting the data in the Total and Volume columns first before applying the PIVOT function to get the final result. My suggestion would be to first write a hard-coded version of the query then convert it to dynamic SQL.

The UNPIVOT process converts these multiple columns into rows. There are a few ways to UNPIVOT, you can use the UNPIVOT function or you can use CROSS APPLY. The code to unpivot the data will be similar to:

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

This gives you data in the format:

+-----+---------------+-------+
| id  |      col      | value |
+-----+---------------+-------+
| DD1 | 2008_A_total  |  1000 |
| DD1 | 2008_A_volume |    10 |
| DD1 | 2008_B_total  |  2000 |
| DD1 | 2008_B_volume |    20 |
| DD1 | 2008_C_total  |  3000 |
| DD1 | 2008_C_volume |    30 |
+-----+---------------+-------+

Then you can apply the PIVOT function:

select ID, 
    [2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
    [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume]
from
(
    select id, 
        col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
        value
    from ATM_TRANSACTIONS t
    cross apply
    (
        select 'total', total union all
        select 'volume', volume
    ) c (col, value)
) d
pivot
(
    max(value)
    for col in ([2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume])
) piv;

Now that you have the correct logic, you can convert this to dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+t_type+'_'+col) 
                    from ATM_TRANSACTIONS t
                    cross apply
                    (
                        select 'total', 1 union all
                        select 'volume', 2
                    ) c (col, so)
                    group by col, so, T_TYPE, T_YEAR
                    order by T_YEAR, T_TYPE, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from 
            (
                select id, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+col, 
                    value
                from ATM_TRANSACTIONS t
                cross apply
                (
                    select ''total'', total union all
                    select ''volume'', volume
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

This will give you a result:

+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| id  | 2008_A_total | 2008_A_volume | 2008_B_total | 2008_B_volume | 2008_C_total | 2008_C_volume | 2009_A_total | 2009_A_volume | 2009_B_total | 2009_B_volume | 2009_C_total | 2009_C_volume |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| DD1 |         1000 |            10 |         2000 |            20 |         3000 |            30 |         4000 |            40 |         5000 |            50 |         6000 |            60 |
| DD2 |         7000 |            70 |         8000 |            80 |         9000 |            90 |        10000 |           100 |        11000 |           110 |         1200 |           120 |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+

Answer 2

declare @stmt nvarchar(max)

select @stmt = isnull(@stmt + ', ', '') + 
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then TOTAL else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_TOTAL') + ',' +
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then VOLUME else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_VOLUME')
from (select distinct T_YEAR, T_TYPE from ATM_TRANSACTIONS) as T
order by T_YEAR, T_TYPE

select @stmt = '
    select
        ID, ' + @stmt + ' from ATM_TRANSACTIONS group by ID'

exec sp_executesql
    @stmt = @stmt

unfortunately, sqlfiddle.com is not working at the moment, so I cannot create an example for you.

The query created by dynamic SQL would be:

select
    ID,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then TOTAL else 0 end) as 2008_A_TOTAL,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then VOLUME else 0 end) as 2008_A_VOLUME,
    ...
from ATM_TRANSACTIONS
group by ID