SQL基础：PIVOT unPIVOT 讲解SQL Server使用PIVOT与unPIVOT实现行列转换

一、sql行转列：PIVOT

1、基本语法：

create table #table1
( id int ,code varchar(10) , name varchar(20) );
go

insert into #table1 ( id,code, name ) values ( 1, ‘m1′,’a’ ), ( 2, ‘m2’,null ), ( 3, ‘m3’, ‘c’ ), ( 4, ‘m2′,’d’ ), ( 5, ‘m1′,’c’ );
go

select * from #table1;

–方法一（推荐）
select PVT.code, PVT.a, PVT.b, PVT.c
from #table1 pivot(count(id) for name in(a, b, c)) as PVT;

–方法二
with P as (select * from #table1)
select PVT.code, PVT.a, PVT.b, PVT.c
from P pivot(count(id) for name in(a, b, c)) as PVT;
drop table #table1;