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
)
?
來自大神張志濤
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
