一、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;结果：
2、实例：
3、传统方式：（先汇总拼接出所需列的字符串，再动态执行转列）
先查询出要转为列的行数据，再拼接字符串。
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;
declare @strCN nvarchar(100);
select @strCN = isnull(@strCN + ",", "") + quotename(name) from #table1 group by name ;
print @strCN --‘[a],[c],[d]"
declare @SqlStr nvarchar(1000);
set @SqlStr = N"
select * from #table1 pivot ( count(ID) for name in (" + @strCN + N") ) as PVT";
exec ( @SqlStr );
drop table #table1;结果：
二、sql列转行：unPIVOT：
基本语法：
create table #table1 (id int,
code varchar(10),
name1 varchar(20),
name2 varchar(20),
name3 varchar(20));
go
insert into #table1(id, name1, name2, code, name3)
values(1, "m1", "a1", "a2", "a3"),
(2, "m2", "b1", "b2", "b3"),
(4, "m1", "c1", "c2", "c3");
go
select * from #table1;
--方法一
select PVT.id, PVT.code, PVT.name, PVT.val
from #table1 unpivot(val for name in(name1, name2, name3)) as PVT;
--方法二
with P as (select * from #table1)
select PVT.id, PVT.code, PVT.name, PVT.val
from P unpivot(val for name in(name1, name2, name3)) as PVT;
drop table #table1;结果：
实例：
到此这篇关于SQL Server使用PIVOT与unPIVOT实现行列转换的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文地址：https://www.cnblogs.com/springsnow/p/10284301.html
