Bagaimana untuk Melakukan Pivoting Dinamik dengan Berbilang Lajur dalam SQL Server 2008?

PIVOT Dinamik dengan Berbilang Lajur dalam SQL Server

Gambaran Keseluruhan

SQL Server menyediakan pilihan terhad untuk memutar data merentas berbilang lajur . Artikel ini meneroka cara untuk mencapai pangsi dinamik dalam SQL Server 2008 dengan lebih daripada satu lajur menggunakan gabungan SQL nyahpivot dan dinamik.

Memahami Masalah

Matlamatnya adalah untuk mengubah jadual dengan berbilang lajur ke dalam format yang lebih mesra pengguna di mana nilai lajur menjadi pengepala lajur. Dalam kes ini, jadual mengandungi data berikut:

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

Output yang dikehendaki ialah:

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

Penyelesaian

Menyahpangsi Data

Untuk memutar berbilang lajur, pertama sekali anda perlu menyahpangsi data. Ini bermakna menukar lajur kepada baris, dengan lajur kol mengenal pasti nama lajur asal dan lajur nilai yang mengandungi nilai.

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

Fungsi PIVOT Dinamik

Langkah seterusnya ialah menggunakan Fungsi PIVOT untuk mengubah data yang tidak dipangsi ke dalam format yang dikehendaki. Walau bagaimanapun, SQL Server mempunyai had apabila memutar berbilang lajur. Untuk mengatasinya, SQL dinamik digunakan untuk membina pertanyaan PIVOT pada masa jalan.

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;

Kesimpulan

Dengan menggabungkan nyahpivot dengan SQL dinamik, adalah mungkin untuk melaksanakan pangsi dinamik merentas berbilang lajur dalam SQL Server 2008. Ini menyediakan penyelesaian yang fleksibel untuk menukar data kepada format yang lebih mesra pengguna.

Atas ialah kandungan terperinci Bagaimana untuk Melakukan Pivoting Dinamik dengan Berbilang Lajur dalam SQL Server 2008?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!