如何在 SQL Server 2008 中执行多列动态透视？

SQL Server 中的多列动态透视

概述

SQL Server 提供了跨多列透视数据的有限选项。本文探讨了如何使用逆透视和动态 SQL 组合在 SQL Server 2008 中实现具有多个列的动态透视。

理解问题

目标是转换具有多个列的表将列转换为更加用户友好的格式，其中列的值成为列标题。在本例中，该表包含以下数据：

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	1200	120

所需的输出为：

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

解决方案

取消数据透视

要旋转多个列，首先需要取消数据透视。这意味着将列转换为行，其中一个 col 列标识原始列名称，一个 value 列包含该值。

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

动态 PIVOT 函数

下一步是使用PIVOT 函数将未透视的数据转换为所需的格式。但是，SQL Server 在旋转多个列时存在限制。为了克服这个问题，使用动态 SQL 在运行时构建 PIVOT 查询。

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;

结论

通过将逆透视与动态 SQL 相结合，可以在 SQL 中跨多个列执行动态透视Server 2008。这提供了一种灵活的解决方案，可以将数据转换为更用户友好的格式。

以上是如何在 SQL Server 2008 中执行多列动态透视？的详细内容。更多信息请关注PHP中文网其他相关文章！