SQL Server, der PIVOT und UNPIVOT zusammen verwendet

Kombinieren Sie die Leistung von UNPIVOT und dann PIVOT, um eine 12-Spalten-Tabelle in eine 36-Tabellenspalte oder mehr zu verwandeln

Aim

These functions together can be combined into a very powerful procedure.

We are going to take the data set from the UNPIVOT article, which has 12 months of data stored on a by row basis, we are the going to UNPIVOT and convert these columns into dates and finally PIVOT this into a table 36 columns wide.

I have written smaller articles on each of these separately, so make sure you have read them, and then lets jump into the code.

Verwendung von UNPIVOT

Kurzanleitung zur Verwendung von UNPIVOT.

Mehr: SQL Server verwendet UNPIVOT, um Spalten in Zeilen umzuwandeln

Verwendung von PIVOT

Kurzanleitung zur Verwendung von PIVOT.

Mehr: SQL Server mit PIVOT-Beispiel

SQL

CREATE TYPE c1bs_UnPivot AS TABLE(UnPivID INT,UnPivMonth DATE,UnPivData INT)GOCREATE FUNCTION c1bs_DateFromParts(@Year SMALLINT,@Month SMALLINT,@Day SMALLINT) RETURNS DATE AS BEGINDECLARE @Date DATE=CONVERT(NVARCHAR(4),@Year)+'-'+CONVERT(NVARCHAR(2),@Month)+'-'+CONVERT(NVARCHAR(2),@Day)RETURN @DateENDGODECLARE @UnPiv TABLE(UnPivID INT,UnPivYear SMALLINT,M1 INT,M2 INT,M3 INT,M4 INT,M5 INT,M6 INT,M7 INT,M8 INT,M9 INT,M10 INT,M11 INT,M12 INT)INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2010,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2011,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2012,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 2,2010,1,2,3,4,5,6,7,8,9,10,11,12
DECLARE @Piv AS c1bs_UnPivotINSERT INTO @PivSELECT UnPivID,dbo.c1bs_DateFromParts(UnPivYear,REPLACE(col,'M',''),1) col,valFROM @UnPivUNPIVOT (Val FOR col IN (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)) unpiv

In the last statement we have followed the same process as in the UNPIVOT article, declared a temporary table to insert a dummy data set into, and then UNPIVOTED data into a temp table.

SQL

--Complex Pivot - unknown column names--Get Column Names for belowDECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) SELECT @Cols=COALESCE(@Cols+',','')+'['+CONVERT(NVARCHAR(20),UnPivMonth)+']',--Concatenate the Columns@Sel =COALESCE(@Sel +',','')+'ISNULL(['+CONVERT(NVARCHAR(20),UnPivMonth)+'], 0) AS ['+CONVERT(NVARCHAR(20),UnPivMonth)+']' --Concatenate the Columns into a select listFROM @Piv GROUP BY UnPivMonth ORDER BY UnPivMonth
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_UnPivot READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [UnPivID],'+@Sel+'FROM @PivPIVOT (SUM(UnPivData)FOR UnPivMonthIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE sp_executesql @SQL,--SQL String from above@Params,--Parameter list@Piv--Temp Table needs to be passed in, can only be read only

In this statement we have followed the same process as in the complex PIVOT example. Declared variables to hold our select list and column names, and injected them into a PIVOT statement to return a data set.

This would return a table with the 36 dates we inserted at the start, in the example below only we only show the first 6 columns. The table would grow to however many dates were provided.

UnPivID	01/01/2010	01/02/2010	01/03/2010	01/04/2010	01/05/2010	01/06/2010
1	1	2	3	4	5	6
2	1	2	3	4	5	6