SQL Server 實現行列（縱橫表）轉換

在平常的工作中或者面試中，我們可能有遇到過資料庫的行列轉換問題。今天我們就來討論下。

1.建立表

首先我們來建立一張表。

sql語句:

--1. 建立資料表
if OBJECT_ID('Score') is not null drop table Score

create table Score
(
    姓名 nvarchar(128),
    課程 nvarchar(128),
    分數 int
)

insert into Score values('張三','語文',98)
insert into Score values('張三','數學',89)
insert into Score values('張三','物理',78)
insert into Score values('李四','語文',79)
insert into Score values('李四','數學',88)
insert into Score values('李四','物理',100)

select * from Score

執行結果：

2. 傳統的行列轉換

2.1 縱錶轉橫表

先看看我們要轉成的橫表張什麼樣子:

既然這個表只有兩列，那麼可以根據姓名進行分組。先把姓名拼湊出來，後面的分數我們再想辦法。

sql:

select t.姓名 2 from Score as t 3 group by t.姓名

結果：

分析：

1. 我們先拿到語文這個科目的分數。既然我們用到了group by 語句，這裡肯定要用聚合函式來求分數。
2. 而且我們只需要語文這一科的成績，分組出來的 一共有 3列 ，分別是 語文、數學、物理  。  那麼就需要判斷科目來取分數。

這裡符合我們需求的 case 語句就登場了。他和c#中switch-case 作用一樣。

sql case 語句語法：

case 欄位
    when 值1 then 結果
    when 值2 then 結果2
    ...
    else 預設結果
end

求語文的分數就簡單了：

select t.姓名,
SUM(case t.課程 when '語文' then t.分數 else 0 end) as 語文
from Score as t
group by t.姓名

結果：

既然語文的分數取到了，其他科目改變下條件就可以了。

完整的sql:

select t.姓名,
SUM(case t.課程 when '語文' then t.分數 else 0 end) as 語文,
SUM(case t.課程 when '數學' then t.分數 else 0 end) as 數學,
SUM(case t.課程 when '物理' then t.分數 else 0 end) as 物理
from Score as t
group by t.姓名

OK,到這兒，我們傳統方式的縱錶轉橫表就大功告成了。

2.2 橫錶轉縱表

那麼我們可以把轉換過來的橫表再轉換回去嗎？

我們先把剛剛轉好的表，插入一個新表ScoreHb 中。

-- 轉換的表插入新表
select t.姓名,
SUM(case t.課程 when '語文' then t.分數 else 0 end) as 語文,
SUM(case t.課程 when '數學' then t.分數 else 0 end) as 數學,
SUM(case t.課程 when '物理' then t.分數 else 0 end) as 物理
into ScoreHb
from Score as t
group by t.姓名

這時ScoreHb 就是我們剛轉換好的橫表，我們再想辦法把他轉回來。

怎麼轉呢？ 一步步來。我們也先把張三和李四的語文成績查出來。

sql:

 --張三李四語文的分數
 select t.姓名,
 '語文' as 課程,
 t.語文 as 分數
 from ScoreHb as t

結果：

還有兩科的資料怎麼辦呢？ 很簡單，我們一個個都查出來，然後用 union all 把他們組合為一張表就可以了。

sql:

-- union all連結3個科目
select t.姓名,
'語文' as 課程,
t.語文 as 分數
from ScoreHb as t
union all
select t.姓名,
'數學' as 課程,
t.數學 as 分數
from ScoreHb as t
union all
select t.姓名,
'物理' as 課程,
t.物理 as 分數
from ScoreHb as t
order by t.姓名 desc

結果：

這樣，我們就把表又變回去了。

但是大家有沒有覺得很麻煩呢？別急，我們有更簡單的辦法。下面為大家介紹pivot關係運算子。

3. 用pivot和unPIVOT運算子進行轉換

pivot是sql server 2005 提供的運算子，所以只要資料庫在05版本以上的都可以使用。主要用於行和列的轉換。

3.1 pivot縱錶轉橫表

sql:

select
    t2.姓名,
    t2.數學,
    t2.物理,
    t2.語文
from Score as t1
pivot (sum(分數) for 課程 in(數學,語文,物理)) as t2

結果：

是不是程式碼簡潔多了。

pivot將原來表中 課程欄位中的 資料行 數學,語文,物理 轉換為列，並用sum取對應列的值。

我們只需要記住它的用法就可以了。

3.2 unpivot 橫錶轉縱表

既然有privot可以縱錶轉橫表。那麼有沒有運算子幫我們轉回來呢？

答案是肯定的，他就是unpivot

sql:

 select
     *
 from
 ScoreHb
 unpivot (分數 for 課程 in (語文,數學,物理)) as t4

結果：

 unpivot 將 語文,數學,物理 列轉為行，分數為新的一列存放對應的值。

是不是比我們之前一個個表查詢拼接，方便了很多。