SQL Server行列相互轉(zhuǎn)換的方法詳解
行轉(zhuǎn)列
創(chuàng)建語句:
create table test1( id int identity(1,1) not null, name varchar(255) null, course varchar(255) null, score int null, ) insert into test1(name, course, score) values ('張三','語文', 80) insert into test1(name, course, score) values ('張三','數(shù)學(xué)', 52) insert into test1(name, course, score) values ('張三','英語', 150) insert into test1(name, course, score) values ('李四','語文', 44) insert into test1(name, course, score) values ('李四','數(shù)學(xué)', 111) insert into test1(name, course, score) values ('李四','英語', 110) insert into test1(name, course, score) values ('王五','語文', 140) insert into test1(name, course, score) values ('王五','數(shù)學(xué)', 80) insert into test1(name, course, score) values ('王五','英語', 92) insert into test1(name, course, score) values ('王五','物理', 77) insert into test1(name, course, score) values ('王五','化學(xué)', 65)
原始數(shù)據(jù):
1、第一種方法:
-- 使用case when then else ,這里也可以使用sum函數(shù) select name, max(case course when '語文' then score else 0 end) as chinese, max(case course when '數(shù)學(xué)' then score else 0 end) as math, max(case course when '英語' then score else 0 end) as english, max(case course when '物理' then score else 0 end) as wuli, max(case course when '化學(xué)' then score else 0 end) as huaxue from test1 group by name
第一種結(jié)果:
2、第二種方法:
-- 使用pivot函數(shù)行轉(zhuǎn)列 select name,max(t.語文)as chinese,max(t.數(shù)學(xué))as math,max(t.英語)as english,max(t.物理)as wuli,max(t.化學(xué))as huaxue from test1 pivot(max(score) for course in(語文,數(shù)學(xué),英語,物理,化學(xué)))t group by name
第二種結(jié)果:
3、第三種方法:
有兩種寫法:
-- 第一種寫法,動(dòng)態(tài)sql拼接,有多少行可以進(jìn)行動(dòng)態(tài)拼接sql,在列不確定的情況下可以使用 declare @sql_str varchar(8000); -- 要執(zhí)行的sql declare @sql_col varchar(8000); select @sql_col = isnull(@sql_col + ',','') + quotename(course) from test1 group by course; print(@sql_col); -- 打印數(shù)值列,不必需 set @sql_str = 'select * from (select name,course,score from test1)p pivot(sum(score) for course IN ( '+ @sql_col +'))as pvt order by pvt.name' print (@sql_str);--打印執(zhí)行的sql exec (@sql_str);-- 執(zhí)行查詢 --第二種寫法 declare @name varchar(100); declare @max varchar(1000); declare @sql nvarchar(4000); select @name= stuff( (select ','+course+'' from test1 group by course for xml path('')),1,1,''); select @max= stuff( (select ',max('+course+') as '+course+'' from test1 group by course for xml path('')),1,1,''); set @sql='select name,'+@max+' from test1 pivot (max(score) for course in('+@name+')) css group by name'; exec(@sql);
第三種結(jié)果:
兩種寫法都是一樣的結(jié)果
4、第四種方法:
-- 使用distinct select distinct a.name, (select score from test1 b where a.name=b.name and b.course='語文' ) as 'chinese', (select score from test1 b where a.name=b.name and b.course='數(shù)學(xué)' ) as 'math', (select score from test1 b where a.name=b.name and b.course='英語' ) as 'english', (select score from test1 b where a.name=b.name and b.course='物理' ) as 'wuli', (select score from test1 b where a.name=b.name and b.course='化學(xué)' ) as 'huaxue' from test1 a
第四種結(jié)果:
列轉(zhuǎn)行
創(chuàng)建語句:
create table test2( id int identity(1,1) not null, name varchar(255) null, chinese int null, math int null, english int null, wuli int null, huaxue int null ) insert into test2(name,chinese,math,english,wuli,huaxue) values ('張三',110,120,85,null,null); insert into test2(name,chinese,math,english,wuli,huaxue) values ('李四',130,88,89,null,null); insert into test2(name,chinese,math,english,wuli,huaxue) values ('王五',93,124,87,98,67);
原始數(shù)據(jù):
1、第一種方法:
union all與union的區(qū)別:
union all對(duì)結(jié)果集不會(huì)去除重復(fù)的結(jié)果,union會(huì)去除重復(fù)的結(jié)果
--第一種寫法: select row_number() over(order by id desc) as id,name,t.course,t.score from( select id,name,course='語文',score=chinese from test2 union all select id,name,course='數(shù)學(xué)',score=math from test2 union all select id,name,course='英語',score=english from test2 union all select id,name,course='物理',score=wuli from test2 union all select id,name,course='化學(xué)',score=huaxue from test2 ) t where score is not null order by id asc -- 下面可以不用執(zhí)行,執(zhí)行上面即可 ,case t.course when '語文' then 1 when '數(shù)學(xué)' then 2 when '英語' then 3 when '物理' then 4 when '化學(xué)' then 5 end -- 第二種寫法: select row_number() over(order by id desc) as id,name,t.course,t.score from( select id,name,'語文' as course, chinese as 'score' from test2 union select id,name,'數(shù)學(xué)' as course, math as 'score' from test2 union select id,name,'英語' as course, english as 'score' from test2 union select id,name,'物理' as course, wuli as 'score' from test2 union select id,name,'化學(xué)' as course, huaxue as 'score' from test2 ) t where score is not null order by id asc -- 下面可以不用執(zhí)行,執(zhí)行上面即可 ,case t.course when '語文' then 1 when '數(shù)學(xué)' then 2 when '英語' then 3 when '物理' then 4 when '化學(xué)' then 5 end
第一種結(jié)果:
兩種寫法結(jié)果都是一樣的
2、第二種方法:
--使用unpivot進(jìn)行列轉(zhuǎn)行 select row_number() over(order by id desc) as id,name,score,course from test2 unpivot( score for course in(chinese,math,english,wuli,huaxue))a
第二種結(jié)果:
以上就是SQL Server行列相互轉(zhuǎn)換的方法詳解的詳細(xì)內(nèi)容,更多關(guān)于SQL Server行列相互轉(zhuǎn)換的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細(xì)過程 圖文
在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細(xì)過程 圖文方法,需要的朋友可以參考下。2011-03-03SQL 特殊狀態(tài)“未知“以及“空值NULL“的判斷
Null值是SQL中的一個(gè)特殊值,表示缺少值或未知值,還有未知(UNKNOWN),表示無法判斷出真或者假,本文主要介紹了SQL 特殊狀態(tài)“未知“以及“空值NULL“的判斷,感興趣的可以了解一下2023-11-11教你幾種在SQLServer中刪除重復(fù)數(shù)據(jù)方法
數(shù)據(jù)庫的使用過程中由于程序方面的問題有時(shí)候會(huì)碰到重復(fù)數(shù)據(jù),重復(fù)數(shù)據(jù)導(dǎo)致了數(shù)據(jù)庫部分設(shè)置不能正確設(shè)置……2010-04-04sqlserver 字段值拼接的實(shí)現(xiàn)示例
拼接字段可以通過多種方法實(shí)現(xiàn),本文主要介紹了sqlserver字段值拼接的實(shí)現(xiàn)示例,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決
SQL Server 對(duì)于數(shù)據(jù)平臺(tái)的開發(fā)者來說越來越友好,下面這篇文章主要給大家介紹了關(guān)于SQLServer中JSON文檔型數(shù)據(jù)的查詢問題的解決方法,需要的朋友可以參考下2021-06-06