MSSQL实现将列转行的技巧

1. 背景介绍

在实际的数据处理过程中，常常需要将列转行，或者将行转列，以满足一些特定的需求。例如，在某些数据分析场景中，数据以列的形式存储，而我们需要将列转为行，以满足某些分析需求。MSSQL是一种强大的关系型数据库管理系统，提供了一些方便的函数和技巧，可以快速进行列转行操作。

2. 列转行技巧

2.1 使用UNPIVOT函数

UNPIVOT函数用于将列旋转为行，可以将多个列转换为一个列，以便进行分析。下面是一个简单的示例：

CREATE TABLE employee (

    id INT,
    name NVARCHAR(50),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);
INSERT INTO employee (id, name, salary, bonus)
VALUES
    (1, '张三', 10000, 2000),
    (2, '李四', 8000, 1500),
    (3, '王五', 12000, 2500);
SELECT *
FROM employee
UNPIVOT
(
    Value FOR Attribute IN (salary, bonus)
) AS unpvt;

执行以上SQL语句，返回结果如下：

id  name    Attribute   Value

1   张三        salary      10000.00
1   张三        bonus       2000.00
2   李四        salary      8000.00
2   李四        bonus       1500.00
3   王五        salary      12000.00
3   王五        bonus       2500.00

上述语句中，UNPIVOT函数中的Value和Attribute是必需的参数。其中Value指示了要旋转的列的名称，Attribute指示了新生成的Value列中的属性名称。在以上示例中，我们将表格的salary和bonus列转换为Attribute列中的属性。

2.2 使用CROSS APPLY函数

CROSS APPLY函数用于将多个表关联起来，将多个表的行联接成单个行。下面是一个示例：

CREATE TABLE employee (

    id INT,
    name NVARCHAR(50),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);
INSERT INTO employee (id, name, salary, bonus)
VALUES
    (1, '张三', 10000, 2000),
    (2, '李四', 8000, 1500),
    (3, '王五', 12000, 2500);
SELECT id, name, Value, Attribute
FROM employee
CROSS APPLY (
    VALUES ('salary', salary), ('bonus', bonus)
) AS CA(Attribute, Value);

执行以上SQL语句，返回结果如下：

id  name    Value       Attribute

1   张三        10000.00    salary
1   张三        2000.00     bonus
2   李四        8000.00     salary
2   李四        1500.00     bonus
3   王五        12000.00    salary
3   王五        2500.00     bonus

在以上示例中，我们将表格的salary和bonus列分别与('salary', salary)和('bonus', bonus)关联。

2.3 使用UNION和CASE语句

UNION语句用于在多个表之间进行联接，在其中一个表中为空的列中填充数据，从而将多个表中的多列合并为单个列。下面是一个示例：

CREATE TABLE employee (

    id INT,
    name NVARCHAR(50),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2)
);
INSERT INTO employee (id, name, salary, bonus)
VALUES
    (1, '张三', 10000, 2000),
    (2, '李四', 8000, NULL),
    (3, '王五', NULL, 2500);
SELECT id, name, 'salary' AS Attribute, salary AS Value
FROM employee
WHERE salary IS NOT NULL
UNION ALL
SELECT id, name, 'bonus' AS Attribute, bonus AS Value
FROM employee
WHERE bonus IS NOT NULL;

执行以上SQL语句，返回结果如下：

id  name    Attribute   Value

1   张三        salary      10000.00
1   张三        bonus       2000.00
2   李四        salary      8000.00
3   王五        bonus       2500.00

在以上示例中，我们使用UNION ALL和CASE语句将salary和bonus列转换为Attribute列中的属性。

3. 总结

本文介绍了MSSQL实现将列转行的三种常用技巧：使用UNPIVOT函数，使用CROSS APPLY函数，以及使用UNION和CASE语句。每个技巧都有其特定的应用场景，可以根据实际的业务需求选择合适的技巧进行列转行操作。