PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别( 在数据库属性->选项->兼容级别改为 90 )
SQL2008 中可以直接使用
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FOR
pivot_column
IN
(
<
column_list
>
)
)
UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FOR
pivot_column
IN
(
<
column_list
>
)
)
典型实例
一、行转列
1 、建立表格
IF
OBJECT_ID
(
'
tb
'
)
IS
NOT
NULL
DROP
TABLE
tb
go
CREATE
TABLE
tb(姓名
VARCHAR
(
10
),课程
VARCHAR
(
10
),分数
INT
)
insert
into
tb
VALUES
(
'
张三
'
,
'
语文
'
,
74
)
insert
into
tb
VALUES
(
'
张三
'
,
'
数学
'
,
83
)
insert
into
tb
VALUES
(
'
张三
'
,
'
物理
'
,
93
)
insert
into
tb
VALUES
(
'
李四
'
,
'
语文
'
,
74
)
insert
into
tb
VALUES
(
'
李四
'
,
'
数学
'
,
84
)
insert
into
tb
VALUES
(
'
李四
'
,
'
物理
'
,
94
)
go
SELECT
*
FROM
tb
go
姓名 课程 分数
---------- ---------- -----------
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
2 、使用SQL Server 2000静态SQL
SELECT
姓名,
max
(
CASE
课程
WHEN
'
语文
'
THEN
分数
ELSE
0
END
) 语文,
max
(
CASE
课程
WHEN
'
数学
'
THEN
分数
ELSE
0
END
) 数学,
max
(
CASE
课程
WHEN
'
物理
'
THEN
分数
ELSE
0
END
) 物理
FROM
tb
GROUP
BY
姓名
3 、使用SQL Server 2000动态SQL
--
SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
--
变量按sql语言顺序赋值
declare
@sqlvarchar
(
500
)
set
@sql
=
'
select姓名
'
select
@sql
=
@sql
+
'
,max(case课程when
'''
+
课程
+
'''
then分数else 0 end)[
'
+
课程
+
'
]
'
from
(selectdistinct课程fromtb)a
--
同from tb group by课程，默认按课程名排序
set
@sql
=
@sql
+
'
from tb group by姓名
'
exec
(
@sql
)
--
使用isnull(),变量先确定动态部分
declare
@sqlvarchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
,
'
,
''
)
+
'
max(case课程when
'''
+
课程
+
'''
then分数else 0 end) [
'
+
课程
+
'
]
'
from
(selectdistinct课程fromtb)asa
set
@sql
=
'
select姓名,
'
+
@sql
+
'
from tb group by姓名
'
exec
(
@sql
)
4 、使用SQL Server 2005静态SQL
SELECT
*
FROM
tb pivot(
MAX
(分数)
FOR
课程
IN
(语文,数学,物理))a
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
5、 使用SQL Server 2005动态SQL
--
使用stuff()
DECLARE
@sql
VARCHAR
(
8000
)
SET
@sql
=
''
--
初始化变量 @sql
SELECT
@sql
=
@sql
+
'
,
'
+
课程
FROM
tb
GROUP
BY
课程
--
变量多值赋值
SET
@sql
=
STUFF
(
@sql
,
1
,
1
,
''
)
--
去掉首个','
SET
@sql
=
'
select * from tb pivot (max(分数) for 课程 in (
'
+
@sql
+
'
))a
'
PRINT
@sql
exec
(
@sql
)
--
或使用isnull()
DECLARE
@sql
VARCHAR
(
8000
)
--
获得课程集合
SELECT
@sql
=
ISNULL
(
@sql
+
'
,
'
,
''
)
+
课程
FROM
tb
GROUP
BY
课程
SET
@sql
=
'
select * from tb pivot (max(分数) for 课程 in (
'
+
@sql
+
'
))a
'
exec
(
@sql
)
二、行转列结果加上总分、平均分
1 、使用SQL Server 2000静态SQL
--
SQL SERVER 2000静态SQL
select姓名,
max
(case课程when
'
语文
'
then分数else0end)语文,
max
(case课程when
'
数学
'
then分数else0end)数学,
max
(case课程when
'
物理
'
then分数else0end)物理,
sum
(分数)总分,
cast
(
avg
(分数
*
1.0
)asdecimal(
18
,
2
))平均分
fromtb
groupby姓名
姓名 语文 数学 物理 总分 平均分
---------- ----------- ----------- ----------- -----------
李四 74 84 94 252 84.00
张三 74 83 93 250 83.33
2 、使用SQL Server 2000动态SQL
--
SQL SERVER 2000动态SQL
declare
@sqlvarchar
(
500
)
set
@sql
=
'
select姓名
'
select
@sql
=
@sql
+
'
,max(case课程when
'''
+
课程
+
'''
then分数else 0 end)[
'
+
课程
+
'
]
'
from
(selectdistinct课程fromtb)a
set
@sql
=
@sql
+
'
,sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名
'
exec
(
@sql
)
3 、使用SQL Server 2005静态SQL
SELECT
m.
*
,
n.总分 ,
n.平均分
FROM
(
SELECT
*
FROM
tb PIVOT(
MAX
(分数)
FOR
课程
IN
( 语文, 数学, 物理 ) ) a
) m ,
(
SELECT
姓名 ,
SUM
(分数) 总分 ,
CAST
(
AVG
(分数
*
1.0
)
AS
DECIMAL
(
18
,
2
)) 平均分
FROM
tb
GROUP
BY
姓名
) n
WHERE
m.姓名
=
n.姓名
4 、使用SQL Server 2005动态SQL
--
使用stuff()
DECLARE
@sql
VARCHAR
(
8000
)
SET
@sql
=
''
--
初始化变量@sql
SELECT
@sql
=
@sql
+
'
,
'
+
课程
FROM
tb
GROUP
BY
课程
--
变量多值赋值
--
同select @sql = @sql + ','+课程 from (select distinct 课程 from tb)a
SET
@sql
=
STUFF
(
@sql
,
1
,
1
,
''
)
--
去掉首个','
SET
@sql
=
'
select m.* , n.总分,n.平均分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (
'
+
@sql
+
'
)) b) m ,
(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名
'
EXEC
(
@sql
)
--
或使用isnull()
DECLARE
@sql
VARCHAR
(
8000
)
SELECT
@sql
=
ISNULL
(
@sql
+
'
,
'
,
''
)
+
课程
FROM
tb
GROUP
BY
课程
SET
@sql
=
'
select m.* , n.总分,n.平均分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (
'
+
@sql
+
'
)) b) m ,
(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n
where m.姓名= n.姓名
'
EXEC
(
@sql
)
二、列转行
1 、建立表格
IF
OBJECT_ID
(
'
tb
'
)
IS
NOT
NULL
DROP
TABLE
tb
go
CREATE
TABLE
tb(姓名
VARCHAR
(
10
),语文
INT
,数学
INT
,物理
INT
)
INSERT
INTO
tb
VALUES
(
'
张三
'
,
74
,
83
,
93
)
INSERT
INTO
tb
VALUES
(
'
李四
'
,
74
,
84
,
94
)
go
SELECT
*
FROM
tb
姓名 语文 数学 物理
---------- ----------- ----------- -----------
张三 74 83 93
李四 74 84 94
2 、使用SQL Server 2000静态SQL
--
SQL SERVER 2000静态SQL。
select
*
from
(
select姓名,课程
=
'
语文
'
,分数
=
语文fromtb
unionall
select姓名,课程
=
'
数学
'
,分数
=
数学fromtb
unionall
select姓名,课程
=
'
物理
'
,分数
=
物理fromtb
) t
orderby姓名,case课程when
'
语文
'
then1when
'
数学
'
then2when
'
物理
'
then3end
姓名 课程 分数
---------- ---- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
2 、使用SQL Server 2000动态SQL
--
SQL SERVER 2000动态SQL。
--
调用系统表动态生态。
declare
@sqlvarchar
(
8000
)
select
@sql
=
isnull
(
@sql
+
'
union all
'
,
''
)
+
'
select姓名, [课程]=
'
+
quotename
(Name,
''''
)
+
'
, [分数] =
'
+
quotename
(Name)
+
'
from tb
'
fromsyscolumns
whereName
!=
'
姓名
'
andID
=
object_id
(
'
tb
'
)
--
表名tb，不包含列名为姓名的其他列
orderbycolid
exec
(
@sql
+
'
order by姓名
'
)
go
3 、使用SQL Server 2005静态SQL
--
SQL SERVER 2005动态SQL
SELECT
姓名 ,
课程 ,
分数
FROM
tb UNPIVOT ( 分数
FOR
课程
IN
(
[
语文
]
,
[
数学
]
,
[
物理
]
) ) t
4 、使用SQL Server 2005动态SQL
--
SQL SERVER 2005动态SQL
DECLARE
@sql
NVARCHAR
(
4000
)
SELECT
@sql
=
ISNULL
(
@sql
+
'
,
'
,
''
)
+
QUOTENAME
(name)
FROM
syscolumns
WHERE
id
=
OBJECT_ID
(
'
tb
'
)
AND
name
NOT
IN
(
'
姓名
'
)
ORDER
BY
colid
SET
@sql
=
'
select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(
'
+
@sql
+
'
))b
'
EXEC
(
@sql
)
来自大神张志涛
