SQL将查询结果横转纵|极客教程

SQL将查询结果横转纵

在处理数据时，有时候我们需要将查询结果从横向展示转换为纵向展示，这种操作在SQL中被称为”行转列”。通常情况下，查询结果都是以行的形式返回给用户，但有时候我们需要将查询结果中的某一列作为新的字段，将每一行中的不同值转换为新的列，这样可以更方便地进行数据分析和展示。

在本文中，我们将详细讨论如何在SQL中实现查询结果的行转列操作，包括使用SQL中的PIVOT函数、使用CASE语句以及使用动态SQL等方法来完成这一任务。

使用PIVOT函数实现行转列

在SQL中，可以使用PIVOT函数来将查询结果横向转换为纵向。PIVOT函数通常用于将行数据旋转并将唯一值转换为列，并且可以使用聚合函数进行汇总。下面我们通过一个示例来演示如何使用PIVOT函数实现行转列操作。

假设我们有一个名为student_scores的表，存储了学生的考试成绩信息，包括学生姓名（student_name）、考试科目（subject）和成绩（score）三个字段。我们希望将不同科目的成绩作为新的列，每一行对应一个学生的所有科目成绩，表格如下：

student_name | subject | score
------------ | ------- | -----
Alice        | Math    | 90
Alice        | English | 85
Bob          | Math    | 95
Bob          | English | 80

我们可以使用PIVOT函数将上面的数据转换成如下形式：

student_name | Math | English
------------ | ---- | -------
Alice        | 90   | 85
Bob          | 95   | 80

下面是使用PIVOT函数实现行转列的SQL语句：

SELECT student_name, [Math], [English]
FROM (
    SELECT student_name, subject, score
    FROM student_scores
) AS src
PIVOT (
    MAX(score)
    FOR subject IN ([Math], [English])
) AS pivoted_table;

上面的SQL语句中，子查询src从student_scores表中获取学生的姓名、科目和成绩信息，然后在使用PIVOT函数时，指定将subject列的不同值（即不同科目）转换为新的列，同时使用MAX函数对成绩进行汇总。最后得到了我们期望的行转列结果。

使用CASE语句实现行转列

除了使用PIVOT函数外，还可以使用CASE语句来实现行转列操作。CASE语句可以在查询结果中根据不同条件来返回不同的值，可以根据需要将查询结果中的行值转换为列值。下面我们通过一个示例来演示如何使用CASE语句对查询结果进行行转列操作。

假设我们有一个名为order_items的表，存储了订单商品的信息，包括订单号（order_id）、商品名称（item_name）和数量（quantity）三个字段。我们希望将每个订单中的不同商品名称作为新的列，每一行对应一个订单中不同商品的数量，表格如下：

order_id | item_name | quantity
-------- | --------- | --------
1        | Apple     | 5
1        | Orange    | 2
2        | Banana    | 3
2        | Apple     | 1

我们可以使用CASE语句将上面的数据转换为如下形式：

order_id | Apple | Orange | Banana
-------- | ----- | ------ | ------
1        | 5     | 2      | 0
2        | 1     | 0      | 3

下面是使用CASE语句实现行转列的SQL语句：

SELECT order_id,
       SUM(CASE WHEN item_name = 'Apple' THEN quantity ELSE 0 END) AS Apple,
       SUM(CASE WHEN item_name = 'Orange' THEN quantity ELSE 0 END) AS Orange,
       SUM(CASE WHEN item_name = 'Banana' THEN quantity ELSE 0 END) AS Banana
FROM order_items
GROUP BY order_id;

上面的SQL语句中，通过CASE语句根据item_name的不同值将数量转换为不同的列值，然后使用SUM函数对结果进行汇总，最后使用GROUP BY对每个订单号进行分组得到行转列的结果。

使用动态SQL实现行转列

在上面的示例中，我们使用PIVOT函数和CASE语句来实现行转列操作，但在实际应用中可能并不清楚需要转换的列有多少个，这时可以使用动态SQL来实现行转列。动态SQL可以根据查询结果中的值动态生成SQL语句，实现转换列的灵活性。下面我们通过一个示例来演示如何使用动态SQL来实现行转列操作。

假设我们有一个名为sales_data的表，存储了销售数据，包括销售日期（sale_date）、销售额（sales_amount）和销售人员（sales_person）三个字段。我们希望将每个销售日期的销售额按销售人员作为新的列，表格如下：

sale_date  | sales_person | sales_amount
---------  | ------------ | ------------
2022-01-01 | Alice        | 100
2022-01-01 | Bob          | 150
2022-01-02 | Alice        | 120
2022-01-02 | Bob          | 130

我们可以使用动态SQL将上面的数据转换为如下形式：

sale_date  | Alice | Bob
---------  | ----- | ---
2022-01-01 | 100   | 150
2022-01-02 | 120   | 130

下面是使用动态SQL实现行转列的SQL语句：

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(sales_person) 
            FROM sales_data
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT sale_date, ' + @cols + ' 
            FROM
            (
                SELECT sale_date, sales_person, sales_amount
                FROM sales_data
           ) x
            PIVOT 
            (
                SUM(sales_amount)
                FOR sales_person IN (' + @cols + ')
            ) p '

EXEC(@query);

上面的SQL语句中，先使用查询动态生成需要转换成列的唯一值@cols，然后使用动态SQL语句将查询结果转换成行列格式@query，最后执行动态SQL获得我们期望的行转列结果。