Aquest article donarà una visió general completa de l'ús dels operadors PIVOT i UNPIVOT a SQL Server. Els operadors PIVOT i UNPIVOT són similars als operadors relacionals que permeten transformant l'expressió amb valors de taula en una altra taula . Tots dos operadors generen informes multidimensionals que ajuden a combinar i comparar una gran quantitat de dades ràpidament.

Podem utilitzar el Operador PIVOT quan necessitem transformar expressions amb valors de taula. Es divideix el valors únics d'una columna a moltes columnes en el resultat final. També agregats els valors de columna restants necessaris en el resultat final. Operador UNPIVOT converteix les dades de columnes d'una expressió amb valors de taula en valors de columna, que és la inversa de PIVOT.

Entenem-ho amb l'ajuda del diagrama senzill que es mostra a continuació:

A la part esquerra d'aquesta figura, podem veure el conjunt de dades original , que té tres columnes: Any, Regió, i Vendes . A continuació, podem veure la taula PIVOT a la part dreta, que es construeix girant Regió (files) al nord i al sud (columnes) . Després de convertir les files en columnes, podem fer un conjunt dels valors de la columna de vendes per a cada intersecció entre les columnes i les files de la taula PIVOT.

Primer creem una taula anomenada pivot_demo per demostrar els operadors PIVOT i UNPIVOT. La instrucció següent crea una taula nova a la nostra base de dades especificada:

 CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int ) 

A continuació, inseriu algunes dades a aquesta taula com a continuació:

 INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500); 

Podem verificar les dades mitjançant la instrucció SELECT. Tindrem la següent sortida:

Operador PIVOT

Aquest operador s'utilitza per girar expressions amb valors de taula. Es va introduir per primera vegada a la versió SQL Server 2005. Converteix dades de files a columnes. Divideix els valors únics d'una columna en moltes columnes i després agrega els valors de columna restants necessaris en el resultat final.

Hem de seguir els passos següents per crear una taula PIVOT:

• Seleccioneu el conjunt de dades base per pivotar.
• Creeu resultats temporals amb l'ajuda d'una taula derivada o CTE (expressió de taula comuna).
• Utilitzeu l'operador PIVOT.

Sintaxi

La sintaxi següent il·lustra l'ús de PIVOT a SQL Server:

 SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>

Si trenquem aquest script, podem veure que té dues seccions separades. La primera secció selecciona les dades de la taula principal, i la segona secció determina com es construirà la taula PIVOT. La segona part també conté algunes paraules clau especials com SUMA, FOR i IN. Vegem el significat d'aquestes paraules clau a l'operador PIVOT.

SUMA

Aquest operador està acostumat sumar els valors de la columna especificada per utilitzar-la a la taula PIVOT. L'hem d'utilitzar amb l'operador PIVOT per obtenir les visualitzacions de columnes agregades per a les seccions de valors.

Descàrrega turbo c++

FOR Paraula clau

Aquesta paraula clau s'utilitza per a la instrucció de la taula PIVOT instruir l'operador PIVOT a quina columna s'ha d'aplicar la funció PIVOT. Bàsicament, indica els noms de columnes que es transformaran de files a columnes.

IN Clau clau

Aquesta paraula clau enumera tots els valors únics de la columna PIVOT per mostrar-se com a columnes de la taula PIVOT.

Exemple

Entenem-ho amb l'ajuda de diversos exemples.

1. La declaració següent selecciona primer les columnes Any, Nord i Sud com a dades base per al pivot. A continuació, creeu un resultat temporal utilitzant la taula derivada i, finalment, apliqueu l'operador PIVOT per generar la sortida final. Aquesta sortida també s'ordena en l'any ascendent.

 SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year 

L'execució d'aquesta instrucció produirà la sortida següent. Aquí, podem veure el suma calculada de les vendes de les regions nord i sud corresponents als valors de l'any .

2. Aquest és un altre exemple on calcularem la suma de vendes de cada Any corresponent als valors de la regió:

 SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region; 

L'execució d'aquesta declaració serà produir un error perquè no podem especificar el valor numèric com a nom de columna directament.

Tanmateix, SQL Server ens permet evitar aquest problema utilitzant els claudàtors abans de cada valor enter. La declaració actualitzada es mostra al fragment de codi següent:

 SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region; 

Aquesta declaració s'ha executat correctament i mostra la suma calculada de vendes per a cada any corresponent als valors de la regió:

3. L'exemple anterior d'obtenir una taula PIVOT és útil quan som conscients de tots els valors de columna PIVOT possibles. Però suposem que l'any vinent s'incrementa el nombre de columnes. Tenint en compte l'exemple anterior, tenim els anys 2010, 2011 i 2012 com a columnes PIVOT. Tanmateix, no hi ha cap garantia que aquestes columnes no es modifiquin en el futur. Què passa si tenim dades del 2013 o del 2014, o potser encara més? En aquests casos, haurem d'utilitzar taula dinàmica PIVOT consultes per solucionar aquest problema.

La consulta dinàmica de la taula PIVOT encapsula tot l'script PIVOT en un procediment emmagatzemat. Aquest procediment proporcionarà opcions ajustables, que ens permetran modificar els nostres requisits canviant uns quants valors parametritzats.

substituïu la cadena a la cadena java

El següent codi SQL explica el funcionament de la taula PIVOT dinàmica. En aquest script, primer hem recuperat tots els valors diferents de la columna PIVOT i després hem escrit una instrucció SQL per executar-la amb la consulta PIVOT en temps d'execució. Vegem la sortida després d'executar aquest script:

 CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END 

En aquest script, hem creat dues variables parametritzades. La seva descripció es presenta a continuació:

@PivotColumn : Aquesta variable prendrà el nom de la columna de la taula original on es crea la taula PIVOT. Per exemple , aquí, la columna 'Regió' mostra totes les regions disponibles a les columnes.

@PivotList : Aquesta variable agafarà la llista de columnes que volem mostrar com a columna de sortida a la taula PIVOT.

Execució del procediment emmagatzemat dinàmic

tokenitzador de cadena java

Després de la creació correcta del procediment emmagatzemat dinàmic, estem preparats per executar-lo. La instrucció següent s'utilitza per cridar el procediment emmagatzemat dinàmic per mostrar la taula PIVOT en temps d'execució:

 EXEC DynamicPivotTable N'Region', N'[North], [South]' 

Aquí hem especificat ara el nom de la columna ' Regió ' com a primer paràmetre i la llista de columnes PIVOT com a segon paràmetre. En executar l'script, es mostrarà la següent sortida:

Ara, podem afegir més columnes en el futur en temps d'execució per mostrar la taula PIVOT, cosa que no és possible amb els dos primers exemples.

Operador UNPIVOT

És el mètode invers de l'operador PIVOT a SQL Server. Aquest operador realitza el funcionament contrari de PIVOT mitjançant la conversió de dades de columnes a files. L'operador UNPIVOT també gira la taula PIVOT a la taula normal. Es va introduir per primera vegada a la versió SQL Server 2005.

Sintaxi

La sintaxi següent il·lustra el UNPIVOT a SQL Server:

 SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name) 

Exemple

Entendrem com desactivar l'operació PIVOT amb exemples. Primer crearem un taula original i taula PIVOT i després aplicat l'operador UNPIVOT en aquesta taula.

El fragment de codi següent declara primer una variable de taula temporal @Tab:

 DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) ) 

A continuació, inserirem els valors en aquesta taula de la següent manera:

 INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year 

Ara, podem realitzar l'operació UNPIVOT mitjançant la instrucció següent:

 SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable 

L'execució del fragment de codi retornarà la sortida següent:

El fragment de codi següent és un altre exemple per realitzar primer l'operació PIVOT i després l'operació UNPIVOT a la mateixa taula dins d'una sola consulta:

 SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable 

En executar el fragment de codi, es mostrarà la mateixa sortida:

NOTA: El procés UNPIVOT és una operació inversa del procediment PIVOT, però no és una inversió exacta. Com que les files s'han combinat quan PIVOT calcula l'agregat i combina moltes files en una sola fila en el resultat, per tant, l'operació UNPIVOT no pot fer que la taula sigui com l'original. Tanmateix, si l'operador PIVOT no fusiona moltes files en una sola fila, l'operador UNPIVOT pot obtenir la taula original de la sortida PIVOT.

Conclusió

Aquest article donarà una visió general completa dels operadors PIVOT i UNPIVOT a SQL Server i convertirà una expressió de taula en una altra. No s'ha d'oblidar mai que UNPIVOT és l'operació inversa de PIVOT, però no és l'inversa exacta del resultat PIVOT.