行列轉換，通常有2種方法，一是CASE WHEN/UNION；一是PIVOT/UNPIVOT。對于行值或列數不固定的情況，需要用動態SQL。
一. 行轉列
--
drop table RowToCol
create
table
RowToCol
(
ID
int
,
Code
varchar
(
10
),
Value
int
)
Go
insert
RowToCol
select
1
,
'
Item1
'
,
1000
union
all
select
1
,
'
Item2
'
,
1000
union
all
select
1
,
'
Item3
'
,
500
union
all
select
2
,
'
Item1
'
,
2000
union
all
select
2
,
'
Item2
'
,
0
union
all
select
3
,
'
Item1
'
,
1000
union
all
select
3
,
'
Item3
'
,
500
GO
select
*
from
RowToCol
? 要得到這樣的結果：
| ID | Item1 | Item2 | Item3 |
| 1 | 1000 | 1000 | 500 |
| 2 | 2000 | 0 | 0 |
| 3 | 1000 | 0 | 500 |
1. CASE WHEN
在SQL Server 2000時，常用的寫法，沿用至今。
(1) 靜態
select
ID,
sum
(
case
Code
when
'
Item1
'
then
Value
else
0
end
)
as
Item1,
sum
(
case
Code
when
'
Item2
'
then
Value
else
0
end
)
as
Item2,
sum
(
case
Code
when
'
Item3
'
then
Value
else
0
end
)
as
Item3
from
RowToCol
group
by
ID
--
或者用max也行
select
ID,
max
(
case
Code
when
'
Item1
'
then
Value
else
0
end
)
as
Item1,
max
(
case
Code
when
'
Item2
'
then
Value
else
0
end
)
as
Item2,
max
(
case
Code
when
'
Item3
'
then
Value
else
0
end
)
as
Item3
from
RowToCol
group
by
ID
?(2) 動態
在不確定有多少行需要轉為列時，先distinct出待轉列的值，再拼出包含若干個CASE的SQL語句，然后運行。
declare
@sql
varchar
(
8000
)
set
@sql
=
'
select ID
'
select
@sql
=
@sql
+
'
, max(case Code when
'''
+
Code
+
'''
then Value else 0 end) [
'
+
Code
+
'
]
'
from
(
select
distinct
Code
from
RowToCol)
as
a
set
@sql
=
@sql
+
'
from RowToCol group by ID
'
--
print @sql
exec
(
@sql
)
?
2. PIVOT
PIVOT是SQL Server 2005的新語法，Oracle在11g中也推出了這個語法。
(1) 靜態
select
*
from
(
select
*
from
RowToCol) a
pivot (
max
(value)
for
Code
in
(
[
Item1
]
,
[
Item2
]
,
[
Item3
]
)) b
?(2) 動態
用PIVOT拼寫動態SQL時就簡單了，只要把后面的列清單整理出來就可以了。
declare
@sql
varchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
],[
'
,
''
)
+
Code
from
RowToCol
group
by
Code
set
@sql
=
'
[
'
+
@sql
+
'
]
'
--
print @sql
exec
(
'
select * from (select * from RowToCol) a pivot (max(value) for Code in (
'
+
@sql
+
'
)) b
'
)
?
二. 列轉行
--
drop table ColToRow
create
table
ColToRow
(
ID
int
,
Item1
int
,
Item2
int
,
Item3
int
)
GO
insert
into
ColToRow
select
'
1
'
,
1000
,
1000
,
500
union
all
select
'
2
'
,
2000
,
0
,
0
union
all
select
'
3
'
,
1000
,
0
,
500
GO
select
*
from
ColToRow
? 要得到這樣的結果：
| ID | Code | Value |
| 1 | Item1 | 1000 |
| 1 | Item2 | 1000 |
| 1 | Item3 | 500 |
| 2 | Item1 | 2000 |
| 2 | Item2 | 0 |
| 2 | Item3 | 0 |
| 3 | Item1 | 1000 |
| 3 | Item2 | 0 |
| 3 | Item3 | 500 |
1. UNION
在SQL Server 2000時，常用的寫法，沿用至今。
(1) 靜態
select
ID,Code
=
'
Item1
'
,Value
=
Item1
from
ColToRow
union
all
select
ID,Code
=
'
Item2
'
,Value
=
Item2
from
ColToRow
union
all
select
ID,Code
=
'
Item3
'
,Value
=
Item3
from
ColToRow
order
by
ID
? SQL Server對于多個UNION的排序，只要在最后加ORDER BY就可以了。
(2) 動態
在不確定有多少列需要轉為行時，先借助系統表syscolumns找出待轉行的列，再拼出包含若干個UNION語句，然后運行。
declare
@sql
varchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
union all
'
,
''
)
+
'
select ID , [Code] =
'
+
quotename
(Name ,
''''
)
+
'
, [Value] =
'
+
quotename
(Name)
+
'
from ColToRow
'
from
syscolumns
where
name
<>
N
'
ID
'
and
ID
=
object_id
(
'
ColToRow
'
)
order
by
colid
asc
--
print @sql
exec
(
@sql
+
'
order by ID
'
)
?
2. UNPIVOT
UNPIVOT是SQL Server 2005的新語法，Oracle在11g中也推出了這個語法。
(1) 靜態
select
ID , Code , Value
from
ColToRow
unpivot (Value
for
Code
in
(
[
Item1
]
,
[
Item2
]
,
[
Item3
]
)) t
?(2) 動態
declare
@sql
varchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
],[
'
,
''
)
+
name
from
syscolumns
where
name
<>
N
'
ID
'
and
ID
=
object_id
(
'
ColToRow
'
)
set
@sql
=
'
[
'
+
@sql
+
'
]
'
--
print @sql
exec
(
'
select ID , Code , Value from ColToRow unpivot (Value for Code in(
'
+
@sql
+
'
)) t
'
)
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
