Utilizzo contemporaneo di PIVOT e UNIPIVOT in funzioni SQL

ombinando la potenza di UNPIVOT e poi di PIVOT per trasformare una tabella a 12 colonne in una tabella a 36 o più colonne

Scopo

Queste funzioni insieme possono essere combinate in una procedura molto potente.

Prenderemo il set di dati dall'articolo UNPIVOT, che contiene 12 mesi di dati archiviati per riga, andremo a UNPIVOT e convertiremo queste colonne in date e infine PIVOT in una tabella larga 36 colonne.

Ho scritto articoli più piccoli su ciascuno di questi separatamente, quindi assicurati di averli letti, quindi passiamo al codice.

Utilizzo di UNPIVOT

Guida rapida all'utilizzo di UNPIVOT.

Altro: SQL Server utilizza UNPIVOT per trasformare le colonne in righe

Utilizzo di PIVOT

Guida rapida all'utilizzo di PIVOT.

Altro: SQL Server utilizzando l'esempio PIVOT

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

Utilizzo di PIVOT

Nell'ultima istruzione abbiamo seguito lo stesso processo dell'articolo UNPIVOT, dichiarato una tabella temporanea in cui inserire un set di dati fittizio e quindi i dati UNPIVOTED in una tabella temporanea.

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

Utilizzo di PIVOT

In questa affermazione abbiamo seguito lo stesso processo dell'esempio complesso PIVOT. Variabili dichiarate per contenere i nomi dell'elenco e delle colonne selezionati e inserite in un'istruzione PIVOT per restituire un set di dati.

Ciò restituirebbe una tabella con le 36 date che abbiamo inserito all'inizio, solo nell'esempio sotto mostriamo solo le prime 6 colonne. La tabella cresceva fino al numero di date fornite.

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