如何在 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中文網其他相關文章！