SQL Pivot: saber com convertir files a columnes

Relacional emmagatzemar grans quantitats de dades en forma de taules. Aquestes taules poden tenir qualsevol nombre de files i columnes. Però, i si haguéssiu de canviar les dades de nivell de fila per dades columnars? Bé, en aquest article sobre SQL Pivot, us mostraré com podeu convertir files a una columna d’un SQL Server.

En aquest article es tractaran els temes següents:

• Què és PIVOT a SQL?
• Sintaxi
• Exemple
• Funcionament de la clàusula PIVOT
• SQL UNPIVOT

Què és PIVOT a SQL?

PIVOT s’utilitza per girar el valor de la taula convertint els valors únics d’una sola columna en diverses columnes. S'utilitza per girar les files a valors de columna i executa agregacions quan es requereix als valors de columna restants.

UNPIVOT, en canvi, s’utilitza per dur a terme les operacions oposades. Per tant, s’utilitza per convertir les columnes d’una taula específica a valors de columna.

Seguint amb aquest article, entenem la sintaxi de SQL Pivot.

Sintaxi:

SELECT Nom de columna no pivotat, [Primer nom de columna pivotat] AS Nom de columna, [Segon nom de columna pivotat] AS Nom de columna, [Tercer nom de columna pivotat] AS Nom de columna, ... [Últim nom de columna pivotat] AS Nom de columna FROM (consulta SELECT que produeix les dades) COM [àlies per a la consulta inicial] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName de la columna els valors de la qual esdevindran capçaleres de columna] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last columna pivotada])) AS [àlies de la taula dinàmica]

Aquí,també podeu utilitzar el fitxer Clàusula ORDER BY per ordenar els valors en ordre ascendent o descendent. Ara que ja sabeu què és PIVOT a SQL i la seva sintaxi bàsica, anem a avançar i veurem com utilitzar-lo.

quan s’ha d’utilitzar a Java

Exemples

Per a una millor comprensió, examinaré la taula següent per explicar-vos tots els exemples.

Taula de proveïdors:

Escrivim una consulta senzilla per recuperar el cost mitjà invertit per cada client.

SELECCIONAR CustomerID, AVG (Cost) COM AverageCostofCustomer DEL GRUP DE PROVEiersDORS PER CustomerID

Sortida:

Ara, diguem que volem pivotar la taula anterior. Aquí, els valors de columna CustomerID es convertiran en capçaleres de columna.

- Creeu una taula dinàmica amb una fila i tres columnes SELECCIONA 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Suppliers) COM a SourceTable PIVOT (AVG (Cost) PER A CustomerID IN ( [11], [22], [33])) AS Taula dinàmica

Sortida:

Nota: Quan utilitzeu funcions agregades amb PIVOT, no es tenen en compte els valors nuls mentre es calcula una agregació.

Bé, aquest va ser un exemple bàsic, però entenguem ara com funcionava la clàusula PIVOT.

Funcionament de la clàusula PIVOT

Com podeu consultar més amunt, per crear una TAULA PIVOT, heu de seguir els passos següents:

• Seleccioneu les columnes per pivotar
• A continuació, seleccioneu una taula d'origen.
• Apliqueu l'operador PIVOT i, a continuació, utilitzeu les funcions agregades.
• Esmenta els valors de pivot.

Seleccioneu les columnes per pivotar

Inicialment, hem d’especificar els camps que s’han d’incloure als nostres resultats. En el nostre exemple, he considerat la columna AverageCostofCustomer de la taula dinàmica. Després vam crear altres tres columnes amb les capçaleres de columna 11, 22 i 33. Exemple-

SELECCIONA 'AverageCostofCustomer' AS Cost_According_To_Customers, [11], [22], [33]

Seleccioneu la taula font

A continuació, heu d'especificar la sentència SELECT que retornarà les dades d'origen de la taula dinàmica. En el nostre exemple, retornem l’identificador de client i el cost de la taula Proveïdors.

(SELECCIONAR ID de client, cost dels proveïdors) COM a taula de fonts

Apliqueu l'operador PIVOT i, a continuació, utilitzeu les funcions agregades

A continuació, heu d'especificar la funció agregada que s'ha d'utilitzar mentre es crea la taula dinàmica. En el nostre exemple, he utilitzat la funció AVG per calcular el cost mitjà.

PIVOT (AVG (cost)

Esmenta els valors de pivot

Finalment, heu d'esmentar els valors que s'han d'incloure a la taula dinàmica resultant. Aquests valors s’utilitzaran com a encapçalaments de columna a la taula dinàmica.

PER A CustomerID IN ([11], [22], [33])) COM a taula dinàmica

Així funcionen els operadors PIVOT. Seguint amb aquest article sobre SQL PIVOT, entenem la diferència que té de SQL UNPIVOT.

SQL UNPIVOT

L'operador SQL UNPIVOT s'utilitza per dur a terme l'operació contrària a la de PIVOT. S'utilitza per girar les dades de la columna en dades a nivell de fila. La sintaxi d'UNPIVOT és similar a la de PIVOT. L'única diferència és que heu d'utilitzar el fitxer ' UNPIVOT ” .

Exemple:

Creem una taula amb les columnes SupplierID, AAA, BBB i CCC. A més, inseriu pocs valors.

CREA TABLA mostrejable (ProveïdorID int, AAA int, BBB int, CCC int) VEURE INSERIT A VALORS mostrejables (1,3,5,6) INSERTAR A VALORS mostrejables (2,9,2,8) INSERTAR A VALORS mostrejables (3, 8,1,7) GO

Sortida:

Ara, diguem-ne, volem desintegrar la taula. Per fer-ho, podeu consultar el codi següent:

SELECCIONAR identificador de proveïdor, clients, productes FROM (SELECCIONAR proveïdorD, AAA, BBB, CCC FROM mostrejable) p UNPIVOT (Productes per a clients IN (AAA, BBB, CCC)) COM exemple GO

Així és com podeu utilitzar SQL PIVOT i UNPIVOT. Amb això, arribem al final d’aquest article. Espero que hagueu entès com utilitzar SQL. Si voleu obtenir més informació sobre MySQL i conegueu aquesta base de dades relacional de codi obert i, a continuació, consulteu la nostra que inclou formació en viu dirigida per un instructor i experiència en projectes reals. Aquesta formació us ajudarà a comprendre en profunditat MySQL i us ajudarà a dominar el tema.

Tens alguna pregunta? Esmenteu-lo a la secció de comentaris d’aquest article sobre SQL Pivot i us respondré.