行列转换，通常有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
'
)
