SqlServer 資料庫行轉列的sql語句 )

問題描述

假設有張學產生績表(CJ)如下
Name Subject Result
張三 語文 80
張三 數學 90
張三 物理 85
李四 語文 85
李四 數學 92
李四 物理 82

現在 想寫 sql 語句     查詢後結果 為   
姓名 語文 數學 物理
張三 80 90 85
李四 85 92 82       該怎麼實現 ？

研究意義

        這是個並不複雜的問題，但卻是資料庫中行轉列的一個典型例子，只要把這個抽象出來的具有普遍意義的問題研究透徹，其他類似的複雜問題迎刃而解。

問題分析

       首先介紹下行轉列的概念，也許書上並沒有這個概念，行轉列說的是這樣一類問題：有時候為了資料庫表的設計滿足使用者的動態要求（比如添加欄位），我們採用定義欄位名表，然後定義一個欄位值的表，這樣就達到了用靜態來表達動態，換句話說就是把資料庫表中本來應該是橫向的延伸轉化為縱向的延伸，再換句話說就是把資料庫表中本來應該是欄位的增加轉化為記錄條數的增加。然而，在這樣設計下，固然靈活，確帶來了統計分析的麻煩，因為統計分析時，應該是以直觀的形式進行表現。換言之，統計分析時，我們又應該顯示為欄位更多的那種。如果同時做到了資料存放區時列的增加轉化為行的增加，資料提取時又可得到列增加了的資料，資料庫表的這種設計就對使用者透明了。

        本文前面提出的這個問題就是一個典型的在資料提取時要把以行增加形式的資料轉化為以列增加形式的資料。為什麼這樣說呢？我們注意subject欄位，subject裡的內容在資料庫儲存時是以不同資料行的形式，換言之，是以行增加的形式，而輸出時，這裡面的內容我們要變成欄位名了。

        衡量這個問題解決好壞我們有幾個標準：1.當資料正好就是上面這個樣子時，解決辦法能否得到正確的解；2.如果增加科目了科目的種類，解決方案是否仍然能行得通；3.如果有些人的某們課程的成績還沒有下來，換言之，資料庫中不是每個人每門課的成績都可以找到，資料庫缺少某個人某門課的成績的記錄。在這種情況下程式還能否得到合理的結果。

實驗環境

       本實驗使用MS SQL Server 2005環境測試。

實驗過程

       1.建立資料表，錄入資料

        CREATE TABLE [dbo].[CJ](
[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[result] [int] NULL,
CONSTRAINT [PK_CJ] PRIMARY KEY CLUSTERED
(
[name] ASC,
[subject] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

     通過可視化介面或者用insert語句錄入資料

     2.第一個最直接，最簡單的做法

         select distinct c.[name] as 姓名,
(select result from CJ where [name] = c.[name] and subject = '語文' )as 語文,
(select result from CJ where [name] = c.[name] and subject = '數學' )as 數學,
(select result from CJ where [name] = c.[name] and subject = '物理' )as 物理
from CJ c

          主要思想就是把任務分成兩步，第一步：把第一列產生出來。第二步：根據第一列每行的姓名取值，查詢該同學的各科成績join到第一步產生的只有一列表。distinct不能省略。

          該方法能夠完成該任務，但只能滿足前文所述的評價標準1和標準3。當科目增多或者實際科目沒有這麼多時統計的結果就不那麼完美了。換言之，這種方法是靜態，將科目在sql語句裡寫死了。另外中間的幾個sql語句查詢效率似乎並不那麼高，還需要掃描整個表，實際上應該只需要在一個學生對應的幾條記錄裡找就可以了。

       3.較好的辦法

      先不管標準2，想想能不能解決那個掃描的效率問題。於是得到了下面的辦法。

      select   [name] as 姓名,
sum(case when subject='語文' then result end) as 語文,
sum(case when subject='數學' then result end) as 數學,
sum(case when subject='物理' then result end) as 物理
from CJ group by [name]

      該辦法大致思想類似前一種。最大的改進是用了group by，由於用了group by後欄位名除了group by的那個其他不能直接用，加了個集合函式，實際上這個Sum只會加一項，因為這個表的主鍵是name + subject。用了group by就會解決掃描的效率問題，因為sum是計算的每個分組之類的。本方法的技巧之處在於case when的使用。

       這個辦法還是不能滿足標準2。

      4.較完美的辦法

     現在就是怎麼解決subject“由死到活”的問題。想到了一種辦法如下：

    declare @s nvarchar(1000)
select @s = 'select [name] as 姓名'
select @s = @s + ',sum(case when subject=''' + cast(subject as varchar) + ''' then result end) as ' + subject from CJ group by subject
select @s = @s + ' from CJ group by [name]'
exec(@s)

     其實思想是基於前面那種辦法的，關鍵的地方就是通過動態產生sql語句，然後執行之。

    在@s的第一次累加中的代碼中一句from CJ group by subject很是有技巧性，可見簡單的select * from table t where .. 也是這麼變化無窮，不得不佩服sql或者說關係型資料庫的智慧。