SQL Server // Do you want to lean how to pivot data in 5 minute?
What do you think about learning more about pivoting?
It is usually not considered a simple topic but it really is!
Go!
So, what is the pivoting operation?
The pivoting operation consists in transforming the data coming from rows of a table into data grouped on several columns.
We can consider the pivoting operation as an operation consisting of these three phases carried out in order:
1. Grouping (we want to have only one row for each distinct element on the rows. It is equivalent to a GROUP BY operation)
2. Spreading (we want to have n columns in which to box the values that we will then aggregate. It is equivalent to a CASE)
3. Aggregating (we aggregate the values on the columns for example with the SUM function)
For example, we could pivot data in this way:
SELECT
SUM (CASE WHEN BR.CODE = 'NIKE' THEN CR.QTA END) AS [QTY_FOR_BRAND_WITH_CODE_NIKE],
SUM (CASE WHEN BR.CODE = 'SNEAKERS' THEN CR.QTA END) AS [QTY_FOR_BRAND_WITH_CODE_SNEAKERS]
FROM ORDRIG CR
JOIN PRODUCTS PR ON CR.IDPRODUCTS = PR.ID
JOIN BRAND BR ON PR.IDBRAND = BR.ID
WHERE CR.IDORDTES = 1016680
GROUP BY CR.IDORDTES
SQL Server introduced the PIVOT (and UNPIVOT) operator in order to avoid having to write a row for each column.
The T-SQL PIVOT command definition is:
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
Now we will try to identify each piece..
A) The <spreading_values> are the list of the columns
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
[QTY_FOR_BRAND_WITH_CODE_NIKE], [QTY_FOR_BRAND_WITH_CODE_SNEAKERS]
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
B) The <source table> is
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
SELECT
<spreading_values>
FROM (
SELECT
SUM(CR.QTA1) AS QTA,
BR.CODICE
FROM ORDRIG CR
JOIN PRODUCTS PR ON CR.IDPRODUCTS = PR.ID
JOIN BRAND BR ON PR.IDBRAND = BR.ID
WHERE CR.IDORDTES = 1016680
GROUP BY BR.CODICE
)
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
C) The <aggregate_function>(<aggregation_element>) is:
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
SELECT
<spreading_values>
FROM <source table>
PIVOT( SUM (QTA)
FOR <spreading_col> IN (<target_cols>) ) <alias>
D) The spreading element is (values that will become header)
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR P.CODE IN (<target_cols>) ) <alias>
E) The list of columns to be displayed are:
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN (<target_cols>) ) <alias>
SELECT
<spreading_values>
FROM <source table>
PIVOT( <aggregate_function>(<aggregation_element>)
FOR <spreading_col> IN ([QTY_FOR_BRAND_WITH_CODE_NIKE], [QTY_FOR_BRAND_WITH_CODE_SNEAKERS]) <alias>
Finally mixing all togheter we obtain…
SELECT
[QTY_FOR_BRAND_WITH_CODE_NIKE], [QTY_FOR_BRAND_WITH_CODE_SNEAKERS]
FROM (
SELECT
SUM(CR.QTA1) AS QTA,
BR.CODICE
FROM ORDRIG CR
JOIN PRODUCTS PR ON CR.IDPRODUCTS = PR.ID
JOIN BRAND BR ON PR.IDBRAND = BR.ID
WHERE CR.IDORDTES = 1016680
GROUP BY BR.CODICE
) P
PIVOT( SUM (QTA)
FOR P.CODE IN ([QTY_FOR_BRAND_WITH_CODE_NIKE], [QTY_FOR_BRAND_WITH_CODE_SNEAKERS]) <alias>
The design of the PIVOT operator requires you to explicitly specify the aggregation and spreading elements, but lets SQL Server implicitly figure out the grouping element by elimination.
Whichever columns appear in the source table that is provided as the input to the PIVOT operator, they implicitly become the grouping element.
That's all for today!
Have a new great week!
Luca
Comments
Post a Comment