How to Perform Dynamic Pivoting on Multiple Columns in SQL Server?

Dynamic Pivoting over Multiple Columns in SQL Server

Problem:

In SQL Server, how can we perform a dynamic pivot over multiple columns in a table, where the column names can change in the future?

Solution:

To achieve dynamic pivoting with multiple columns, follow these steps:

1. Unpivot the Data:

• Unpivot the columns you want to pivot using UNPIVOT or CROSS APPLY.
• In our example, we want to unpivot the "Total" and "Volume" columns:

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);

2. Apply PIVOT Function:

• Apply the PIVOT function to the unpivoted data, specifying the column names you want to pivot to.
• In our case, we want to pivot to years and types:

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;

3. Convert to Dynamic SQL:

• If the column names can change in the future, you can convert the above query to dynamic SQL using dynamic pivot T-SQL (e.g., sp_executesql).
• The following dynamic SQL query generates the PIVOT statement based on the existing table structure:

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;

Output:

This will generate a pivoted result with the specified columns. The results may vary depending on the data in your table.

The above is the detailed content of How to Perform Dynamic Pivoting on Multiple Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!