table 行轉(zhuǎn)列的sql詳解
更新時間:2009年06月23日 18:28:18 作者:
tabele行轉(zhuǎn)列的資料,網(wǎng)上搜一下很多。大家照著網(wǎng)上copy就可以實(shí)現(xiàn)自己想要的功能。但是大家在實(shí)現(xiàn)功能后是否想過行轉(zhuǎn)列為什么要這樣寫?下面就以一個實(shí)例來分析, 希望對初學(xué)者有所幫助。
一、要求
1 創(chuàng)建數(shù)據(jù)表
CREATE TABLE [dbo].[StuScore](
[stuid] [int] NOT NULL,
[subject] [nvarchar](30) NULL,
[score] [decimal](5, 1) NULL
)
2 插入測試數(shù)據(jù)
stuid subject score
3 chinese 76.0
3 math 73.0
4 chinese 82.0
5 chinese 66.0
5 math 93.0
6 chinese 67.0
7 math 83.0
8 chinese 77.0
8 math 84.0
3 行轉(zhuǎn)列后的結(jié)果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
二 、分析
1 行轉(zhuǎn)列,一個重點(diǎn)就是怎么樣知道有多少列,怎么樣創(chuàng)建這些列?我們可以先把這個問題擱置,而假設(shè)這些列是已知的。 例如示例數(shù)據(jù)中,可以先假設(shè)subject的數(shù)據(jù)[chinese,math]是已知的,這樣問題就簡化了許多
2 當(dāng)已知了chinese,math后,我們至少要先得到轉(zhuǎn)換后的tabel結(jié)構(gòu)
如下;
select stuid, 0 as chinese, 0 as math from dbo.StuScore
結(jié)果如下
stuid chinese math
3 0 0
3 0 0
4 0 0
5 0 0
5 0 0
6 0 0
7 0 0
8 0 0
8 0 0
3 接著就需要往這個數(shù)據(jù)集中去填充chinese, math的數(shù)據(jù)
select stuid,
case subject when 'chinese' then score else 0 end as chinese,
case subject when 'math' then score else 0 end as math
from dbo.StuScore
結(jié)果如下:
stuid chinese math
3 76.0 0.0
3 0.0 73.0
4 82.0 0.0
5 66.0 0.0
5 0.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 0.0
8 0.0 84.0
4 細(xì)心的讀者會發(fā)現(xiàn)步驟3中的結(jié)果與我們想要的已經(jīng)非常接近了,只需再做一個sum()處理,就OK了
select stuid,
sum(case subject when 'chinese' then score else 0 end ) as chinese,
sum(case subject when 'math' then score else 0 end ) as math
from dbo.StuScore group by stuid
得到的正是我們想要的結(jié)果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
是不是現(xiàn)在就已經(jīng)完成了呢?答案是否定的。前面我們已經(jīng)說過,是為了簡化問題,在假設(shè)已經(jīng)知道了subject數(shù)據(jù)的情況下,這么處理的,實(shí)際上subject的數(shù)據(jù)是可變的,未知的,接下來就是要解決這個問題了
5 要獲取subject的數(shù)據(jù)其實(shí)很簡單
select distinct subject from dbo.StuScore
獲取以后怎樣得到case subject when 'chinese' then score else 0 end 這種語句?
可以根據(jù)subject的值去動態(tài)的組sql語句
看下面的一段代碼
declare @sql varchar(2000)
set @sql=''
select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject
from (select distinct subject from dbo.StuScore) as sub
print @sql
message打印的信息如下:
,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math
6 最后我們就需要將前面步驟綜合起來,得到最終的sql
declare @sql varchar(2000)
set @sql='select stuid'
select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject
from (select distinct subject from dbo.StuScore) as sub
set @sql=@sql + ' from dbo.StuScore group by stuid'
exec(@sql)
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
至此,整個分析過程和結(jié)果就都出來了。
初試寫文章, 多包涵,指正。
1 創(chuàng)建數(shù)據(jù)表
CREATE TABLE [dbo].[StuScore](
[stuid] [int] NOT NULL,
[subject] [nvarchar](30) NULL,
[score] [decimal](5, 1) NULL
)
2 插入測試數(shù)據(jù)
stuid subject score
3 chinese 76.0
3 math 73.0
4 chinese 82.0
5 chinese 66.0
5 math 93.0
6 chinese 67.0
7 math 83.0
8 chinese 77.0
8 math 84.0
3 行轉(zhuǎn)列后的結(jié)果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
二 、分析
1 行轉(zhuǎn)列,一個重點(diǎn)就是怎么樣知道有多少列,怎么樣創(chuàng)建這些列?我們可以先把這個問題擱置,而假設(shè)這些列是已知的。 例如示例數(shù)據(jù)中,可以先假設(shè)subject的數(shù)據(jù)[chinese,math]是已知的,這樣問題就簡化了許多
2 當(dāng)已知了chinese,math后,我們至少要先得到轉(zhuǎn)換后的tabel結(jié)構(gòu)
如下;
select stuid, 0 as chinese, 0 as math from dbo.StuScore
結(jié)果如下
stuid chinese math
3 0 0
3 0 0
4 0 0
5 0 0
5 0 0
6 0 0
7 0 0
8 0 0
8 0 0
3 接著就需要往這個數(shù)據(jù)集中去填充chinese, math的數(shù)據(jù)
select stuid,
case subject when 'chinese' then score else 0 end as chinese,
case subject when 'math' then score else 0 end as math
from dbo.StuScore
結(jié)果如下:
stuid chinese math
3 76.0 0.0
3 0.0 73.0
4 82.0 0.0
5 66.0 0.0
5 0.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 0.0
8 0.0 84.0
4 細(xì)心的讀者會發(fā)現(xiàn)步驟3中的結(jié)果與我們想要的已經(jīng)非常接近了,只需再做一個sum()處理,就OK了
select stuid,
sum(case subject when 'chinese' then score else 0 end ) as chinese,
sum(case subject when 'math' then score else 0 end ) as math
from dbo.StuScore group by stuid
得到的正是我們想要的結(jié)果
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
是不是現(xiàn)在就已經(jīng)完成了呢?答案是否定的。前面我們已經(jīng)說過,是為了簡化問題,在假設(shè)已經(jīng)知道了subject數(shù)據(jù)的情況下,這么處理的,實(shí)際上subject的數(shù)據(jù)是可變的,未知的,接下來就是要解決這個問題了
5 要獲取subject的數(shù)據(jù)其實(shí)很簡單
select distinct subject from dbo.StuScore
獲取以后怎樣得到case subject when 'chinese' then score else 0 end 這種語句?
可以根據(jù)subject的值去動態(tài)的組sql語句
看下面的一段代碼
declare @sql varchar(2000)
set @sql=''
select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject
from (select distinct subject from dbo.StuScore) as sub
print @sql
message打印的信息如下:
,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math
6 最后我們就需要將前面步驟綜合起來,得到最終的sql
declare @sql varchar(2000)
set @sql='select stuid'
select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject
from (select distinct subject from dbo.StuScore) as sub
set @sql=@sql + ' from dbo.StuScore group by stuid'
exec(@sql)
stuid chinese math
3 76.0 73.0
4 82.0 0.0
5 66.0 93.0
6 67.0 0.0
7 0.0 83.0
8 77.0 84.0
至此,整個分析過程和結(jié)果就都出來了。
初試寫文章, 多包涵,指正。
您可能感興趣的文章:
- mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實(shí)例詳解
- sql語句實(shí)現(xiàn)行轉(zhuǎn)列的3種方法實(shí)例
- SQLServer行轉(zhuǎn)列實(shí)現(xiàn)思路記錄
- MySQL存儲過程中使用動態(tài)行轉(zhuǎn)列
- mssql 數(shù)據(jù)庫表行轉(zhuǎn)列,列轉(zhuǎn)行終極方案
- Sql Server 2000 行轉(zhuǎn)列的實(shí)現(xiàn)(橫排)
- SQL查詢語句行轉(zhuǎn)列橫向顯示實(shí)例解析
- sql動態(tài)行轉(zhuǎn)列的兩種方法
- SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解
- SQL基礎(chǔ)教程之行轉(zhuǎn)列Pivot函數(shù)
相關(guān)文章
SQL Server臨時表的正確刪除方式(sql server 刪除臨時表)
臨時表與一般的表不同,它是保存到tempDb表中。臨時表的表名與你所建的表名也不一樣,因?yàn)樗獮椴煌说南嗤僮鲃?chuàng)建不同的臨時表。下文將為您分別示例正確和錯誤的刪除操作,感興趣的朋友一起看看吧2023-02-02SQL查詢中出現(xiàn)笛卡爾積現(xiàn)象的解決方法
本文主要介紹了SQL查詢中出現(xiàn)笛卡爾積現(xiàn)象的解決方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05DataGrip 格式化SQL的實(shí)現(xiàn)方法(自定義Sql格式化)
DataGrip 是 JetBrains 發(fā)布的多引擎數(shù)據(jù)庫環(huán)境,本文主要介紹了如何自定義Sql格式化風(fēng)格,具有一定的參考價值,感興趣的可以了解一下2021-09-09SQL Server數(shù)據(jù)庫復(fù)制失敗的原因及解決方法
本文我們主要介紹了SQL Server數(shù)據(jù)庫中由于mssqlserver沒有停止造成數(shù)據(jù)庫復(fù)制失敗的解決方法,需要的朋友可以參考下2015-08-08分發(fā)服務(wù)器 系統(tǒng)拋出18483錯誤,未能連接服務(wù)器,因?yàn)?'distributor_admin''未定義遠(yuǎn)程登陸
錯誤18483,未能連接服務(wù)器,因?yàn)?'distributor_admin '未在該服務(wù)器上定義遠(yuǎn)程登錄。2010-07-07