在平常的工作中或者面試中，我們可能有遇到過數據庫的縱橫表的轉換問題。今天我們就來討論下。
1.創建表
首先我們來創建一張表。
sql語句:
1
--
1. 創建數據表
2
if
OBJECT_ID
(
'
Score
'
)
is
not
null
drop
table
Score
3
4
create
table
Score
5
(
6
姓名
nvarchar
(
128
),
7
課程
nvarchar
(
128
),
8
分數
int
9
)
10
11
insert
into
Score
values
(
'
張三
'
,
'
語文
'
,
98
)
12
insert
into
Score
values
(
'
張三
'
,
'
數學
'
,
89
)
13
insert
into
Score
values
(
'
張三
'
,
'
物理
'
,
78
)
14
insert
into
Score
values
(
'
李四
'
,
'
語文
'
,
79
)
15
insert
into
Score
values
(
'
李四
'
,
'
數學
'
,
88
)
16
insert
into
Score
values
(
'
李四
'
,
'
物理
'
,
100
)
17
18
select
*
from
Score
執行結果：
2. 傳統的縱橫表轉換
2.1 縱表轉橫表
先看看我們要轉成的橫表張什么樣子:
既然這個表只有兩列，那么可以根據姓名進行分組。先把姓名拼湊出來，后面的分數我們再想辦法。
sql:
select
t.姓名
2
from
Score
as
t
3
group
by
t.姓名
結果：
?
分析：
- 我們先拿到語文這個科目的分數。既然我們用到了group by 語句，這里肯定要用聚合函數來求分數。
- 而且我們只需要語文這一科的成績，分組出來的 一共有 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?中。
1
--
轉換的表插入新表
2
select
t.姓名,
3
SUM
(
case
t.課程
when
'
語文
'
then
t.分數
else
0
end
)
as
語文,
4
SUM
(
case
t.課程
when
'
數學
'
then
t.分數
else
0
end
)
as
數學,
5
SUM
(
case
t.課程
when
'
物理
'
then
t.分數
else
0
end
)
as
物理
6
into
ScoreHb
7
from
Score
as
t
8
group
by
t.姓名
這時ScoreHb 就是我們剛轉換好的橫表，我們再想辦法把他轉回來。
怎么轉呢？ 一步步來。我們也先把張三和李四的語文成績查出來。
sql:
1
--
張三李四語文的分數
2
select
t.姓名,
3
'
語文
'
as
課程,
4
t.語文
as
分數
5
from
ScoreHb
as
t
結果：
還有兩科的數據怎么辦呢？ 很簡單，我們一個個都查出來，然后用 union all 把他們組合為一張表就可以了。
sql:
1
--
union all鏈接3個科目
2
select
t.姓名,
3
'
語文
'
as
課程,
4
t.語文
as
分數
5
from
ScoreHb
as
t
6
union
all
7
select
t.姓名,
8
'
數學
'
as
課程,
9
t.數學
as
分數
10
from
ScoreHb
as
t
11
union
all
12
select
t.姓名,
13
'
物理
'
as
課程,
14
t.物理
as
分數
15
from
ScoreHb
as
t
16
order
by
t.姓名 desc
?
結果：
?
這樣，我們就把表又變回去了。
但是大家有沒有覺得很麻煩呢？別急，我們有更簡單的辦法。下面為大家介紹pivot關系運算符。
3. 用pivot和unpivot運算符進行轉換
pivot是sql server 2005 提供的運算符，所以只要數據庫在05版本以上的都可以使用。主要用于行和列的轉換。
3.1 pivot縱表轉橫表
sql:
1
select
2
t2.姓名,
3
t2.數學,
4
t2.物理,
5
t2.語文
6
from
Score
as
t1
7
pivot (
sum
(分數)
for
課程
in
(數學,語文,物理))
as
t2
結果：
?
是不是代碼簡潔多了。
pivot將原來表中 課程字段中的 數據行 數學,語文,物理 轉換為列，并用sum取對應列的值。
我們只需要記住它的用法就可以了。
?
3.2 unpivot 橫表轉縱表
既然有privot可以縱表轉橫表。那么有沒有運算符幫我們轉回來呢？
答案是肯定的，他就是unpivot
?
sql:
1
select
2
*
3
from
4
ScoreHb
5
unpivot (分數
for
課程
in
(語文,數學,物理))
as
t4
結果：
?unpivot?將 語文,數學,物理 列轉為行，分數為新的一列存放對應的值。
?是不是比我們之前一個個表查詢拼接，方便了很多。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
