table行轉列的sql詳解

table行轉列的sql詳解
tabele行轉列的資料，網上搜一下很多。大家照著網上copy就可以實現自己想要的功能。但是大家在實現功能後是否想過行轉列為什麼要這樣寫？下面就以一個執行個體來分析， 希望對初學者有所協助。

一、要求
1 建立資料表
CREATE TABLE [dbo].[StuScore](
    [stuid] [int] NOT NULL,        
    [subject] [nvarchar](30) NULL,
    [score] [decimal](5, 1) NULL
)

2 插入測試資料
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 行轉列後的結果
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 行轉列，一個重點就是怎麼樣知道有多少列，怎麼樣建立這些列？我們可以先把這個問題擱置，而假設這些列是已知的。 例如樣本資料中，可以先假設subject的資料[chinese,math]是已知的，這樣問題就簡化了許多

2 當已知了chinese,math後，我們至少要先得到轉換後的tabel結構
如下；
select stuid, 0 as chinese, 0 as math from dbo.StuScore
結果如下
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 接著就需要往這個資料集中去填充chinese, math的資料
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
結果如下：
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 細心的讀者會發現步驟3中的結果與我們想要的已經非常接近了，只需再做一個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
得到的正是我們想要的結果
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

是不是現在就已經完成了呢？答案是否定的。前面我們已經說過，是為了簡化問題，在假設已經知道了subject資料的情況下，這麼處理的，實際上subject的資料是可變的，未知的，接下來就是要解決這個問題了

5 要擷取subject的資料其實很簡單
select distinct subject from dbo.StuScore
擷取以後怎樣得到case subject when 'chinese' then score else 0 end 這種語句？
可以根據subject的值去動態組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