Bagaimana untuk Melakukan Pivoting Dinamik pada Berbilang Lajur dalam SQL Server?

Pemusingan Dinamik pada Berbilang Lajur dalam Pelayan SQL

Masalah:

Dalam Pelayan SQL, bagaimana kita boleh melakukan pangsi dinamik pada berbilang lajur dalam jadual, di mana nama lajur boleh berubah dalam masa hadapan?

Penyelesaian:

Untuk mencapai pangsi dinamik dengan berbilang lajur, ikut langkah berikut:

1. Nyahpaut Data:

• Nyahpaut lajur yang anda mahu pangsi menggunakan UNPIVOT atau CROSS APPLY.
• Dalam contoh kami, kami ingin menyahpaut "Jumlah" dan "Volume " lajur:

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. Guna Fungsi PIVOT:

• Gunakan fungsi PIVOT pada data yang tidak dipivot, dengan menyatakan nama lajur yang anda ingin pivot.
• Dalam kes kami, kami mahu pivot ke tahun dan jenis:

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. Tukar kepada SQL Dinamik:

• Jika nama lajur boleh berubah pada masa hadapan, anda boleh menukar pertanyaan di atas kepada SQL dinamik menggunakan T-SQL pangsi dinamik (cth., sp_executesql).
• Pertanyaan SQL dinamik berikut menjana pernyataan PIVOT berdasarkan jadual sedia ada struktur:

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:

Ini akan menjana hasil pivoted dengan lajur yang ditentukan. Keputusan mungkin berbeza bergantung pada data dalam jadual anda.

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