sqlserver行转列和列转行

一、行转列

原理

SQL Server中的行转列操作是通过使用PIVOT和UNPIVOT关系运算符来完成的。

PIVOT操作符将表值表达式中的某一列中的唯一值转换为输出中的多个列，并在必要时对最终输出中所需的任何其余列值执行聚合。这使得多行的数据可以按照指定的列名进行重新分组，并且每行数据的每个列都可以进行汇总统计。使用PIVOT操作符进行行转列的基本语法如下：

SELECT <非透视的列>,  
       [第一个透视的列] AS <列名称>,  
       [第二个透视的列] AS <列名称>,  
       ...  
       [最后一个透视的列] AS <列名称>  
FROM (<生成数据的 SELECT 查询>) AS <源查询的别名>  
PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列]) ) AS <透视表的别名>  
<可选的 ORDER BY 子句>;

UNPIVOT操作符则与PIVOT操作相反，它将表值表达式的列转换为列值。这使得在行被合并后，可以在结果中展示原始表值表达式的多行数据。使用UNPIVOT操作符进行行转列的基本语法如下：

SELECT [KeyColumn], [ValueColumn]  
FROM  
(  
  SELECT [ID], [Column1], [Column2], [Column3]  
  FROM [YourTable]  
) t  
UNPIVOT  
(  
  [Value] FOR [Column] IN ([Column1], [Column2], [Column3])  
) u;

需要注意的是，UNPIVOT操作并不完全是PIVOT的反向操作。虽然可以将行旋转为列，但是UNPIVOT不会像PIVOT那样执行聚合操作，而是将多行数据展示为多列数据。在UNPIVOT操作中，输入中的NULL值在输出中也会消失，这意味着如果原始数据中存在NULL值，那么这些NULL值在执行UNPIVOT操作后将不再出现在输出结果中。

探索

在SQL Server中，将行数据转换为列数据可以使用多种方法。以下是两种常用的方法：

方法一：使用PIVOT操作符

SELECT   
    [Column1], [Column2], [Column3]  
FROM   
    (SELECT   
        [RowID], [ColumnName], [ColumnValue]  
    FROM   
        [YourTable]) AS SourceTable  
PIVOT  
(  
    MAX([ColumnValue])  
    FOR [ColumnName] IN ([Column1], [Column2], [Column3])  
) AS PivotTable;

在上面的代码中，将[YourTable]替换为实际的表名。[RowID]是源表中的行标识符列，[ColumnName]是要转置的列名，[ColumnValue]是要转置的值。在PIVOT操作符中，使用MAX([ColumnValue])聚合函数将值转置为新的列。FOR [ColumnName] IN ([Column1], [Column2], [Column3])指定了要转置的列名。

方法二：使用CASE语句和聚合函数

SELECT   
    MAX(CASE WHEN [ColumnName] = 'Column1' THEN [ColumnValue] END) AS [Column1],  
    MAX(CASE WHEN [ColumnName] = 'Column2' THEN [ColumnValue] END) AS [Column2],  
    MAX(CASE WHEN [ColumnName] = 'Column3' THEN [ColumnValue] END) AS [Column3]  
FROM   
    [YourTable];

在上面的代码中，将[YourTable]替换为实际的表名。[ColumnName]是要转置的列名，[ColumnValue]是要转置的值。使用CASE语句和聚合函数将值转置为新的列。

这些方法中的每一个都有其特定的用途和限制

范例

以下是使用PIVOT操作符将行数据转换为列数据的示例：

假设我们有一个名为Sales的表，其中包含以下列：SalesID、Product和QuantitySold。该表用于记录每个销售产品的数量。

表结构如下：

SalesID	Product	QuantitySold
1	A	10
2	B	5
3	C	3
4	A	8
5	B	2
6	C	7
现在，我们希望将每个产品的销售数量按照产品类别进行转置，以便更容易进行分析和比较。以下是使用PIVOT操作符完成此任务的示例代码

SELECT *  
FROM  
(  
  SELECT SalesID, Product, QuantitySold  
  FROM Sales  
) AS SourceTable  
PIVOT  
(  
  SUM(QuantitySold)  
  FOR Product IN ([A], [B], [C]) -- 这里列出所有可能的产品类别  
) AS PivotTable;

执行上述代码后，将返回以下结果：

A B C
18 7 10
上述结果将每个产品的销售数量转置为单独的列，使得我们可以更轻松地对不同产品的销售数据进行比较和分析。

实战

行转列

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go

SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a

列转行

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb

--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t

二、列转行

在SQL Server中，将列转换为行通常涉及使用UNPIVOT操作符。UNPIVOT允许你将列转换为行。以下是使用UNPIVOT的基本语法

SELECT [KeyColumn], [ValueColumn]  
FROM  
(  
  SELECT [ID], [Column1], [Column2], [Column3]  
  FROM [YourTable]  
) t  
UNPIVOT  
(  
  [Value] FOR [Column] IN ([Column1], [Column2], [Column3])  
) u;

在上面的语法中，[YourTable]是你要进行转换的表名，[ID]是可选的键列，[Column1], [Column2], [Column3]是要进行转换的列。这个查询将返回一个包含键列和值列的结果集，其中值列包含从列转换为行的值。

以下是一个具体的示例：

-- 创建一个示例表  
CREATE TABLE MyTable (ID INT, Column1 VARCHAR(10), Column2 VARCHAR(10), Column3 VARCHAR(10));  
  
-- 插入一些数据  
INSERT INTO MyTable (ID, Column1, Column2, Column3)  
VALUES (1, 'A', 'B', 'C'), (2, 'D', 'E', 'F'), (3, 'G', 'H', 'I');  
  
-- 使用UNPIVOT将列转换为行  
SELECT ID, Column, Value  
FROM  
(  
  SELECT ID, Column1, Column2, Column3  
  FROM MyTable  
) t  
UNPIVOT  
(  
  Value FOR Column IN (Column1, Column2, Column3)  
) u;

在上面的示例中，我们创建了一个名为MyTable的表，其中包含ID、Column1、Column2和Column3列。然后，我们使用UNPIVOT将列转换为行，并将结果按ID、Column和Value列返回。