sql將查詢結果橫轉縱，sql server 縱橫表的轉換

　　在平常的工作中或者面試中，我們可能有遇到過數據庫的縱橫表的轉換問題。今天我們就來討論下。

1.創建表

　　首先我們來創建一張表。

sql將查詢結果橫轉縱、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縱向數據變橫向？先看看我們要轉成的橫表張什么樣子:

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

sql時間格式轉換yyyymm、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?將 語文,數學,物理 列轉為行，分數為新的一列存放對應的值。

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

?