sql server行转列（pivot），列转行（unpivot）

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。与 PIVOT 执行的操作相反，UNPIVOT 将表值表达式的列轮换为列值。PIVOT 提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具

浊尘

3745人浏览 · 2022-06-09 03:24:39

浊尘 · 2022-06-09 03:24:39 发布

说明

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。与 PIVOT 执行的操作相反，UNPIVOT 将表值表达式的列轮换为列值。

PIVOT 提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。

语法

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

PIVOT （行转列）


create table #person(id int,num int)

insert into #person values(1,157)
insert into #person values(2,182)
insert into #person values(3,175)
insert into #person values(4,165)
insert into #person values(5,178)

select *from #person


select '身高'as highs ,[1],[2],[3],[4],[5] from 
(select id,num from #person) s 
pivot (avg(num)foR id in([1],[2],[3],[4],[5])  ) as p

原表

pivot 后

highs	1	2	3	4	5
身高	157	182	175	165	178

列转行（unpivot）

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,  
    Emp3 INT, Emp4 INT, Emp5 INT);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  

select *from pvt
 
SELECT VendorID, Employee, Orders  
FROM   
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
UNPIVOT  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;  
GO

原表

VendorID	Emp1	Emp2	Emp3	Emp4	Emp5
1	4	3	5	4	4
2	4	1	5	5	5
3	4	3	5	4	4
4	4	2	5	5	4
5	5	1	5	5	5

unpivot 后

VendorID	Employee	Orders
1	Emp1	4
1	Emp2	3
1	Emp3	5
1	Emp4	4
1	Emp5	4
2	Emp1	4
2	Emp2	1
2	Emp3	5
2	Emp4	5
2	Emp5	5
3	Emp1	4
3	Emp2	3
3	Emp3	5
3	Emp4	4
3	Emp5	4
4	Emp1	4
4	Emp2	2
4	Emp3	5
4	Emp4	5
4	Emp5	4
5	Emp1	5
5	Emp2	1
5	Emp3	5
5	Emp4	5
5	Emp5	5