Bagaimana untuk Pivot Berbilang Lajur secara Dinamik dalam Pelayan SQL?

Transformasi Pangsi Dinamik pada Berbilang Lajur dalam Pelayan SQL

Ikhtisar

Artikel ini menangani cabaran melaksanakan operasi pangsi dinamik pada berbilang lajur dalam Microsoft Pelayan SQL. Matlamatnya adalah untuk mengubah data menjadi format jadual pangsi, dengan baris mewakili nilai yang berbeza dalam lajur yang ditentukan dan lajur mewakili nilai daripada lajur lain.

Menyahpivot Data

Sebelum melaksanakan operasi pangsi, data mesti dinyahpangsi. Proses ini membentuk semula data dengan menukar berbilang lajur kepada baris. Dalam kes ini, lajur Jumlah dan Kelantangan dinyahpaut.

SELECT ID, 
    CONCAT(T_YEAR, '_', T_TYPE, '_', COL) AS COL, 
    VALUE
FROM ATM_TRANSACTIONS t
CROSS APPLY
(
    SELECT 'total', TOTAL UNION ALL
    SELECT 'volume', VOLUME
) c (COL, VALUE)

Menjana Pertanyaan Pangsi Dinamik

Setelah data dinyahpaut, pertanyaan pangsi dinamik boleh dibina. Pertanyaan ini membina lajur yang diperlukan untuk jadual pangsi secara dinamik berdasarkan data tidak dipivot.

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(T_YEAR, '_', 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, 
                    CONCAT(T_YEAR, ''_''+T_TYPE+''_''+COL) AS 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 ';

Melaksanakan Pertanyaan Dinamik

Pertanyaan dinamik dilaksanakan menggunakan prosedur tersimpan sp_executesql. Prosedur ini mengambil pertanyaan yang dijana oleh kod SQL dinamik dan melaksanakannya.

EXECUTE SP_EXECUTESQL @QUERY;

Hasil

Keluaran operasi pangsi dinamik ialah jadual pangsi dengan nilai ID yang berbeza sebagai baris dan lajur mewakili nilai Jumlah dan Kelantangan tidak berpaksi untuk setiap gabungan T_YEAR dan T_TYPE. Keputusannya adalah seperti berikut:

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

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