SQL Server : dynamic pivot over 5 columns

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,     valuefrom ATM_TRANSACTIONS tcross 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)) dpivot(    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 |+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+

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 Torder by T_YEAR, T_TYPEselect @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_TRANSACTIONSgroup by ID

Please try:

DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(T_YEAR+'_'+T_TYPE+'_TOTAL')+','+QUOTENAME(T_YEAR+'_'+T_TYPE+'_VOLUME') from ATM_TRANSACTIONS GROUP BY T_YEAR, T_TYPEIF ISNULL(@pivv, '')<>''    SET @Query='SELECT * FROM(        SELECT ID, T_YEAR+''_''+T_TYPE+''_TOTAL'' TYP, TOTAL VAL from ATM_TRANSACTIONS UNION        SELECT ID, T_YEAR+''_''+T_TYPE+''_VOLUME'' TYP, VOLUME VAL from ATM_TRANSACTIONS         )x pivot (SUM(VAL) for TYP in ('+@pivv+')) as xx'IF ISNULL(@Query, '')<>''    EXEC (@Query)