亲宝软件园·资讯

展开

SQL Server行列转换

springsnow 人气:5

一、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实现行列转换的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。

加载全部内容

相关教程
猜你喜欢
用户评论