将行与列相互转换

在 SQL Server 数据库中清洗数据

Miriam Antona

Software Engineer

电子表格中的数据透视表

非常常见
基于特定列集分组数据
计算其他列的统计量

使用 PIVOT

PIVOT：将某列的唯一值转为多列。

使用 PIVOT —— 将产品名转为列

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             |
| ...                | ...          | ...           |

使用 PIVOT —— 将产品名转为列

将

| 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             |
| ...                | ...          | ...           |

转换为

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

使用 PIVOT —— 将产品名转为列

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

使用 PIVOT —— 将产品名转为列

SELECT
    year_of_sale,
    notebooks,
    pencils,
    crayons

使用 PIVOT —— 将产品名转为列

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 —— 将产品名转为列

| 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    |

使用 PIVOT —— 将产品名转为列

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)

使用 PIVOT —— 将产品名转为列

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))

使用 PIVOT —— 将产品名转为列

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

使用 PIVOT —— 将产品名转为列

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

使用 UNPIVOT

UNPIVOT：将列转为行。

SELECT * FROM pivot_sales

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

使用 UNPIVOT —— 将产品名转为行

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      |

Vamos praticar!

在 SQL Server 数据库中清洗数据