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 |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+