Transformarea rândurilor în coloane și invers

Curățarea datelor în bazele de date SQL Server

Miriam Antona

Software Engineer

Tabele pivot în foi de calcul

Foarte comune
Permit gruparea datelor după un set specific de coloane
Calculează statistici pentru alte coloane

Utilizarea PIVOT

PIVOT: transformă valorile unice dintr-o coloană în coloane multiple.

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT * FROM paper_shop_monthly_sales

| product_name_units | year_of_sale | month_of_sale |
|--------------------|--------------|---------------|
| notebooks-150      | 2018         | 1             |
| notebooks-200      | 2019         | 1             |
| notebooks-30       | 2019         | 2             |
| pencils-100        | 2018         | 1             |
| pencils-50         | 2018         | 2             |
| pencils-130        | 2019         | 1             |
| crayons-80         | 2018         | 1             |
| ...                | ...          | ...           |

Utilizarea PIVOT - Transformarea numelor de produse în coloane

Transformare din

| product_name_units | year_of_sale | month_of_sale |
|--------------------|--------------|---------------|
| notebooks-150      | 2018         | 1             |
| notebooks-200      | 2019         | 1             |
| pencils-50         | 2018         | 2             |
| crayons-80         | 2018         | 1             |
| ...                | ...          | ...           |

în

| year_of_sale | notebooks | pencils | crayons |
|--------------|-----------|---------|---------|
| 2018         |  150      |  150    |  80     |
| 2019         |  230      |  130    |  170    |

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons
FROM
    (SELECT
    year_of_sale,
    SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) AS product_name,
    CAST(SUBSTRING(product_name_units,
        charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units
    FROM paper_shop_monthly_sales) AS sales
PIVOT (SUM(units)
FOR product_name IN (notebook, pencils, crayons))
AS paper_shop_pivot

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons
FROM
    (SELECT
    year_of_sale,
    SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) AS product_name,
    CAST(SUBSTRING(product_name_units,
        charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units
    FROM paper_shop_monthly_sales) AS sales

Utilizarea PIVOT - Transformarea numelor de produse în coloane

| year_of_sale | product_name | units |
|--------------|--------------|-------|
| 2018         | notebooks    | 150   |
| 2019         | notebooks    | 200   |
| 2019         | notebooks    | 30    |
| 2018         | pencils      | 100   |
| 2018         | pencils      | 50    |
| 2019         | pencils      | 130   |
| 2018         | crayons      | 80    |
| 2019         | crayons      | 90    |
| 2019         | crayons      | 80    |

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons
FROM
    (SELECT
    year_of_sale,
    SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) AS product_name,
    CAST(SUBSTRING(product_name_units,
        charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units
    FROM paper_shop_monthly_sales) AS sales
PIVOT (SUM(units)

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons
FROM
    (SELECT
    year_of_sale,
    SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) AS product_name,
    CAST(SUBSTRING(product_name_units,
        charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units
    FROM paper_shop_monthly_sales) AS sales
PIVOT (SUM(units)
FOR product_name IN (notebook, pencils, crayons))

Utilizarea PIVOT - Transformarea numelor de produse în coloane

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons
FROM
    (SELECT
    year_of_sale,
    SUBSTRING(product_name_units, 1, charindex('-', product_name_units)-1) AS product_name,
    CAST(SUBSTRING(product_name_units,
        charindex('-', product_name_units)+1, len(product_name_units)) AS INT) units
    FROM paper_shop_monthly_sales) AS sales
PIVOT (SUM(units)
FOR product_name IN (notebook, pencils, crayons))
AS paper_shop_pivot

Utilizarea PIVOT - Transformarea numelor de produse în coloane

| year_of_sale | notebooks_units | pencils_units | crayons_units |
|--------------|-----------------|---------------|---------------|
| 2018         |  150            |  150          |  80           |
| 2019         |  230            |  130          |  170          |

Utilizarea UNPIVOT

UNPIVOT: Transformă coloanele în rânduri.

SELECT * FROM pivot_sales

| year_of_sale | notebooks | pencils | crayons |
|--------------|-----------|---------|---------|
| 2018         |  150      |  150    |  80     |
| 2019         |  230      |  130    |  170    |

Utilizarea UNPIVOT - Transformarea numelor de produse în rânduri

SELECT * FROM pivot_sales
UNPIVOT
    (units FOR product_name IN (notebooks, pencils, crayons)
) AS unpvt

| year_of_sale | units | product_name |
|--------------|-------|--------------|
| 2018         | 150   | notebooks    |
| 2018         | 150   | pencils      |
| 2018         | 80    | crayons      |
| 2019         | 230   | notebooks    |
| 2019         | 130   | pencils      |
| 2019         | 170   | crayons      |

Să exersăm!

Curățarea datelor în bazele de date SQL Server