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;
结果:
先查询出要转为列的行数据,再拼接字符串。
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;
结果:
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实现行列转换的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
相关文章:
1. Can’t connect to MySQL server on ’localhost’ (10048)2. SQL Server系统函数介绍3. SQL Server开发智能提示插件SQL Prompt介绍4. SQL Server序列SEQUENCE用法介绍5. 轻量级数据库SQL Server Express LocalDb介绍6. SQL Server2019安装的详细步骤实战记录(亲测可用)7. SQL Server数据库备份和恢复数据库的全过程8. SQL Server备份数据库的完整步骤9. SQL Server实现查询每个分组的前N条记录10. 详解SQL Server 中的 ACID 属性