(轉至CSDN-愛新覺羅.毓華)堅持SQL Server的學習, 列轉行這個也是比較常見SQL操作本人愚見, 總結出來具有以下條件的情況下可能會出現此需求: 一個表中某一個列值依賴於該表中兩個或多個列(如下表中的成績依賴於學生和課程), 好像這種情況違反了資料庫設計的第一範式(1NF), 哈哈!今天CSDN上無意看到一個比較全面的列轉行, 但是以前沒怎麼接觸心裡就有種恐懼,現在系統的學習下,避免以後遇見相同的問題的又束手無策了!還是開始建立測試環境 use tempdb;
go
if object_id('tb') is not null
drop table tb
go
create table tb(name varchar(10) , course varchar(10) , scores int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
go
select * from tb
挺簡單的一張表,描述了學生的課程成績資訊,其中有3個欄位,依次是姓名,課程,分數測試資料中有2個學生,分別是張三和李四,而課程有語文,數學,物理3課,查詢結果集如下 現有這一需求, 將成績轉為行顯示,學科為列, 結果如下 結果改變了什麼, 行數改變了,跟學生數量有關,一個學生一行,這裡就肯定是用學生姓名來分組了除姓名外,增加了3列,跟課程數量有關,根據不同的課程建立新列, 這裡就是用 "case when"了,代碼如下:
--靜態實現
select name 姓名,
max(case course when '語文' then scores end) 語文,
max(case course when '數學' then scores end) 數學,
max(case course when '物理' then scores end) 物理
from tb
group by name 這裡的then後的值都是成績, 沒有區別; 只是條件不同, 屬於哪一科的成績就放在那一列下, 因為是漸進式掃描, 每次只有單個成績, 所以這裡的max函數只是為了騙過group分組的幌子, 沒有任何意義, 執行查詢結果如上所示 現將需求稍微小小改變一下, 課程為行學生為列成績不變在中間, 其實這與上面的一個問題類似, 都是將成績以行的形式顯示, 只是"case when" 和 group不一樣而已, 知道原理了, 稍微變通一下, 應該沒問題, 哈哈! select course 課程,
max(case name when '張三' then scores end) 張三,
max(case name when '李四' then scores end) 李四
from tb
group by course
--動態實現以上雖然實現了功能, 但是比較死板, 它是靜態, 也就是課程只允許有語文, 數學, 物理, 若增加了其他課程就不行了, 這樣我們得動態產生sql語句, 才能根據不同的課程產生不同的sql, 然後執行, 以前從未接觸了動態產生sql, 呵呵! 這次看到了才發現了sql編程與開發語言編程有異曲同工之妙, 請看下面動態產生的SQL語句
declare @sql varchar(8000)
set @sql = 'select name 姓名'
select @sql = @sql + ' , max(case course when ''' + course + ''' then scores else 0 end) ' + course + ''
from (select distinct course from tb) as a
set @sql = @sql + ' from tb group by name'
print @sql
動態拼接後的語句與我們之前手寫的結果一樣, 然後通過系統預存程序sp_executesql執行, 進行sql查詢, 效果一樣, 這裡用的一個技巧是select @sql變數賦值, 擷取去重後所有的科目的集合, 然後迴圈動態構造case when語句添加到@sql變數後面, 達到我們想要的效果, 這樣也能正確執行. 下次再來看看SQL 2005中的實現
