Wie führe ich dynamisches Pivotieren mit mehreren Spalten in SQL Server 2008 durch?

Dynamisches Pivotieren mit mehreren Spalten in SQL Server

Übersicht

SQL Server bietet begrenzte Optionen zum Pivotieren von Daten über mehrere Spalten hinweg . In diesem Artikel wird untersucht, wie Sie in SQL Server 2008 eine dynamische Pivotierung mit mehr als einer Spalte mithilfe einer Kombination aus nichtpivotierendem und dynamischem SQL erreichen.

Das Problem verstehen

Das Ziel besteht darin, eine Tabelle mit mehreren Spalten umzuwandeln Spalten in ein benutzerfreundlicheres Format umwandeln, bei dem die Werte der Spalten zu Spaltenüberschriften werden. In diesem Fall enthält die Tabelle folgende Daten:

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

Die gewünschte Ausgabe ist:

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

Lösung

Pivotierung der Daten aufheben

Um mehrere Spalten zu Pivotieren, müssen Sie zunächst die Pivotierung der Daten aufheben. Dies bedeutet, die Spalten in Zeilen umzuwandeln, wobei eine Spalte „col“ den ursprünglichen Spaltennamen identifiziert und eine Wertspalte den Wert enthält.

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

Dynamische PIVOT-Funktion

Der nächste Schritt besteht darin, die zu verwenden PIVOT-Funktion zum Umwandeln der nicht gepivotierten Daten in das gewünschte Format. Allerdings weist SQL Server beim Pivotieren mehrerer Spalten Einschränkungen auf. Um dies zu überwinden, wird dynamisches SQL verwendet, um die PIVOT-Abfrage zur Laufzeit zu erstellen.

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;

Fazit

Durch die Kombination von Unpivoting mit dynamischem SQL ist es möglich, dynamisches Pivotieren über mehrere Spalten in SQL durchzuführen Server 2008. Dies bietet eine flexible Lösung zum Umwandeln von Daten in ein benutzerfreundlicheres Format.

Das obige ist der detaillierte Inhalt vonWie führe ich dynamisches Pivotieren mit mehreren Spalten in SQL Server 2008 durch?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!