文章詳情頁
SQL Server使用PIVOT與unPIVOT實現(xiàn)行列轉換
瀏覽:267日期:2023-03-06 14:25:24
一、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、傳統(tǒng)方式:(先匯總拼接出所需列的字符串,再動態(tài)執(zhí)行轉列)
先查詢出要轉為列的行數(shù)據(jù),再拼接字符串。
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實現(xiàn)行列轉換的文章就介紹到這了。希望對大家的學習有所幫助,也希望大家多多支持。
標簽:
MsSQL
相關文章:
1. SQL Server EXEC和sp_executesql的區(qū)別2. 終級解決SQL Server 2005 未正常卸載,重新安裝問題3. SQL Server中合并用戶日志表的方法4. SQL Server2022安裝圖文教程(最新推薦)5. SQL SERVER中的流程控制語句6. Microsoft SQL Server 7.0安裝問題(二)7. 用SQL Server為Web瀏覽器提供圖像(二)(轉)8. SQL Server數(shù)據(jù)庫中Date/Time 時間格式的轉換9. SQL Server數(shù)據(jù)庫超級管理員賬號防護知識10. SQL Server下7種“數(shù)據(jù)分頁”方案全網(wǎng)最新最全
排行榜

網(wǎng)公網(wǎng)安備