SQL Server使用PIVOT與unPIVOT實現行列轉換

一、sql行轉列：PIVOT

1、基本語法：

create table #table1    (    id int ,code varchar(10) , name varchar(20) );goinsert into #table1 ( id,code, name ) values ( 1, "m1","a" ), ( 2,  "m2",null ), ( 3, "m3", "c" ), ( 4,  "m2","d" ), ( 5,  "m1","c" );goselect * 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 Ppivot(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) );goinsert into #table1 ( id,code, name ) values ( 1, "m1","a" ), ( 2,  "m2",null ), ( 3, "m3", "c" ), ( 4,  "m2","d" ), ( 5,  "m1","c" );goselect * 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));goinsert into #table1(id, name1, name2, code, name3)values(1, "m1", "a1", "a2", "a3"),    (2, "m2", "b1", "b2", "b3"),    (4, "m1", "c1", "c2", "c3");goselect * 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實現行列轉換的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。