1.简介
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。
通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行
2.例题
--建立销售表
CREATE TABLE Sell
(
[Year] INT,
[Quarter] NVARCHAR(10),
Quantity INT
)
--插入测试数据
INSERT INTO Sell VALUES ( 2006, 'Q1', 20 )
INSERT INTO Sell VALUES ( 2006, 'Q2', 15 )
INSERT INTO Sell VALUES ( 2006, 'Q2', 4 )
INSERT INTO Sell VALUES ( 2006, 'Q3', 12 )
INSERT INTO Sell VALUES ( 2006, 'Q4', 18 )
INSERT INTO Sell VALUES ( 2007, 'Q1', 10 )
INSERT INTO Sell VALUES ( 2007, 'Q2', 10 )
INSERT INTO Sell VALUES ( 2008, 'Q1', 8 )
INSERT INTO Sell VALUES ( 2008, 'Q2', 7 )
INSERT INTO Sell VALUES ( 2008, 'Q3', 5 )
INSERT INTO Sell VALUES ( 2008, 'Q3', 10 )
INSERT INTO Sell VALUES ( 2008, 'Q4', 9 )
GO
Year Quarter Quantity
----------- ---------- -----------
2006 Q1 20
2006 Q2 15
2006 Q2 4
2006 Q3 12
2006 Q4 18
2007 Q1 10
2007 Q2 10
2008 Q1 8
2008 Q2 7
2008 Q3 5
2008 Q3 10
2008 Q4 9
(12 行受影响)
--得到每年每季度的销售总数
SELECT *
FROM Sell PIVOT ( SUM(Quantity) FOR [Quarter] IN ( Q1, Q2, Q3, Q4 ) ) AS P
GO
--查询得如下结果
--注意：
--如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。
Year Q1 Q2 Q3 Q4
2006 20 19 12 18
2007 10 10 NULL NULL
2008 8 7 15 9
其实PIVOT在sql2000中可以用SELECT...CASE语句来实现，下面是sql2000的代码：
--sql 2000 静态版本
SELECT [year],
SUM(CASE WHEN [Quarter] = 'Q1' THEN Quantity END) AS Q1,
SUM(CASE WHEN [Quarter] = 'Q2' THEN Quantity END) AS Q2,
SUM(CASE WHEN [Quarter] = 'Q3' THEN Quantity END) AS Q3,
SUM(CASE WHEN [Quarter] = 'Q4' THEN Quantity END) AS Q4
FROM sell
GROUP BY [year]
--sql 2000 动态版本
DECLARE @sql NVARCHAR(2000)
SELECT @sql = 'select [year] '
SELECT @sql = @sql + ',sum(case when [Quarter] =''' + [Quarter]
+ ''' then Quantity end) as ' + [Quarter]
FROM sell
GROUP BY [Quarter]
ORDER BY [Quarter] ASC
select @sql = @sql + ' from sell group by [year] '
execute ( @sql )
UNPIVOT将与PIVOT执行几乎完全相反的操作，将列转换为行。
--创建测试表
CREATE TABLE TestUNPIVOT
(
ID INT,
A1 NVARCHAR(10),
A2 NVARCHAR(10),
A3 NVARCHAR(10)
)
--插入测试数据
INSERT INTO TestUNPIVOT VALUES ( 1, 'q1', 'q2', 'q3' )
INSERT INTO TestUNPIVOT VALUES ( 2, 'q1', 'p1', 'm1' )
INSERT INTO TestUNPIVOT VALUES ( 3, 't1', 'p1', 'm1' )
GO
--UNPIVOT
SELECT ID,
A,
[Value]
FROM ( SELECT ID,
A1,
A2,
A3
FROM TestUNPIVOT
) p UNPIVOT ( [Value] FOR A IN ( A1, A2, A3 ) )AS u
ORDER BY id ASC,
a ASC
GO
--查询得如下结果
ID A Value
1 A1 q1
1 A2 q2
1 A3 q3
2 A1 q1
2 A2 p1
2 A3 m1
3 A1 t1
3 A2 p1
3 A3 m1
--UNPIVOT 的sql 2000 实现语句：
SELECT id,
'a1' AS [A],
a1 AS [Value]
FROM TestUNPIVOT
UNION ALL
SELECT id,
'a2',
A2
FROM TestUNPIVOT
UNION ALL
SELECT id,
'a3',
A3
FROM TestUNPIVOT
ORDER BY id ASC, a ASC
3.总结
个人感觉PIVOT运算符相比SELECT...CASE语句就是代码精简了一些,似乎PIVOT可读性好像不太好！
至少我看起来PIVOT语法有点怪怪，也许是还习惯吧！我个人还是喜欢用SELECT...CASE语句.
希望微软能提供PIVOT运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串
=================================================================================
SQL 行列转换 （PIVOT和UNPIVOT运算符 ）
http://www.cnblogs.com/aierong/archive/2008/09/03/1281777.html
1.简介
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，
并在必要时对最终输出中所需的任何其余列值执行聚合。
UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。
通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行
2.例题
--建立销售表
CREATE TABLE Sell
(
[Year] INT,
[Quarter] NVARCHAR(10),
Quantity INT
)
--插入测试数据
INSERT INTO Sell VALUES ( 2006, 'Q1', 20 )
INSERT INTO Sell VALUES ( 2006, 'Q2', 15 )
INSERT INTO Sell VALUES ( 2006, 'Q2', 4 )
INSERT INTO Sell VALUES ( 2006, 'Q3', 12 )
INSERT INTO Sell VALUES ( 2006, 'Q4', 18 )
INSERT INTO Sell VALUES ( 2007, 'Q1', 10 )
INSERT INTO Sell VALUES ( 2007, 'Q2', 10 )
INSERT INTO Sell VALUES ( 2008, 'Q1', 8 )
INSERT INTO Sell VALUES ( 2008, 'Q2', 7 )
INSERT INTO Sell VALUES ( 2008, 'Q3', 5 )
INSERT INTO Sell VALUES ( 2008, 'Q3', 10 )
INSERT INTO Sell VALUES ( 2008, 'Q4', 9 )
GO
Year Quarter Quantity
----------- ---------- -----------
2006 Q1 20
2006 Q2 15
2006 Q2 4
2006 Q3 12
2006 Q4 18
2007 Q1 10
2007 Q2 10
2008 Q1 8
2008 Q2 7
2008 Q3 5
2008 Q3 10
2008 Q4 9
(12 行受影响)
--得到每年每季度的销售总数
SELECT *
FROM Sell PIVOT ( SUM(Quantity) FOR [Quarter] IN ( Q1, Q2, Q3, Q4 ) ) AS P
GO
--查询得如下结果
--注意：
--如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。
Year Q1 Q2 Q3 Q4
2006 20 19 12 18
2007 10 10 NULL NULL
2008 8 7 15 9
其实PIVOT在sql2000中可以用SELECT...CASE语句来实现，下面是sql2000的代码：
--sql 2000 静态版本
SELECT [year],
SUM(CASE WHEN [Quarter] = 'Q1' THEN Quantity END) AS Q1,
SUM(CASE WHEN [Quarter] = 'Q2' THEN Quantity END) AS Q2,
SUM(CASE WHEN [Quarter] = 'Q3' THEN Quantity END) AS Q3,
SUM(CASE WHEN [Quarter] = 'Q4' THEN Quantity END) AS Q4
FROM sell
GROUP BY [year]
--sql 2000 动态版本
DECLARE @sql NVARCHAR(2000)
SELECT @sql = 'select [year] '
SELECT @sql = @sql + ',sum(case when [Quarter] =''' + [Quarter]
+ ''' then Quantity end) as ' + [Quarter]
FROM sell
GROUP BY [Quarter]
ORDER BY [Quarter] ASC
select @sql = @sql + ' from sell group by [year] '
execute ( @sql )
UNPIVOT将与PIVOT执行几乎完全相反的操作，将列转换为行。
--创建测试表
CREATE TABLE TestUNPIVOT
(
ID INT,
A1 NVARCHAR(10),
A2 NVARCHAR(10),
A3 NVARCHAR(10)
)
--插入测试数据
INSERT INTO TestUNPIVOT VALUES ( 1, 'q1', 'q2', 'q3' )
INSERT INTO TestUNPIVOT VALUES ( 2, 'q1', 'p1', 'm1' )
INSERT INTO TestUNPIVOT VALUES ( 3, 't1', 'p1', 'm1' )
GO
--UNPIVOT
SELECT ID,
A,
[Value]
FROM ( SELECT ID,
A1,
A2,
A3
FROM TestUNPIVOT
) p UNPIVOT ( [Value] FOR A IN ( A1, A2, A3 ) )AS u
ORDER BY id ASC,
a ASC
GO
--查询得如下结果
ID A Value
1 A1 q1
1 A2 q2
1 A3 q3
2 A1 q1
2 A2 p1
2 A3 m1
3 A1 t1
3 A2 p1
3 A3 m1
--UNPIVOT 的sql 2000 实现语句：
SELECT id,
'a1' AS [A],
a1 AS [Value]
FROM TestUNPIVOT
UNION ALL
SELECT id,
'a2',
A2
FROM TestUNPIVOT
UNION ALL
SELECT id,
'a3',
A3
FROM TestUNPIVOT
ORDER BY id ASC, a ASC
3.总结
个人感觉PIVOT运算符相比SELECT...CASE语句就是代码精简了一些,似乎PIVOT可读性好像不太好！
至少我看起来PIVOT语法有点怪怪，也许是还习惯吧！我个人还是喜欢用SELECT...CASE语句.
希望微软能提供PIVOT运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串
------------------------------------------------------------------------------------------------------------------------------------------------------------
----
在SQL Server 2005中，使用关键字PIVOT/UNPIVOT，可以很容易的实现行列转换的需求。现在将通过两个简单的例子详细讲解PIVOT/UNPIVOT的用法。
PIVOT的用法：
首先创建测试表，然后插入测试数据 :
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test
id name quarter profile
----------- -------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
使用PIVOT将四个季度的利润转换成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
-------- --------- ----------- -------- ------- -------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT的用法：
首先建立测试表，然后插入测试数据:
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)
select * from test
id name Q1 Q2 Q3 Q4
-------- ------- --------- --------- -------- --------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- ----------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
今天看到有牛人用另一种方法实现了同样的效果，转来学习学习.
【http://www.cnblogs.com/zhanglei644213943/archive/2009/12/27/1633356.html】
DROP TABLE #STUDENT
CREATE TABLE #student (stdname nvarchar(10),stdsubject nvarchar(10),result int)
INSERT INTO #student VALUES ('张三','语文',80)
INSERT INTO #student values ('张三','数学',90)
INSERT INTO #student VALUES ('张三','物理',85)
INSERT INTO #student VALUES ('李四','语文',85)
INSERT INTO #student values ('李四','数学',92)
INSERT INTO #student VALUES ('李四','物理',82)
INSERT INTO #student VALUES ('李四','化学',82)
INSERT INTO #student VALUES ('李四','化学',82)
SELECT * FROM #student
select stdname,
isnull(sum(case stdsubject when '化学' then result end),0) [化学],
isnull(sum(case stdsubject when '物理' then result end),0) [物理],
isnull(sum(case stdsubject when '语文' then result end),0) [语文],
isnull(sum(case stdsubject when '数学' then result end),0) [数学]
from #student
group by stdname
或者：
declare @sql varchar(4000)
set @sql='select stdname'
select
@sql=@sql+
',isnull(sum(case stdsubject when'''+stdsubject+'''then result end),0)['+stdsubject+']'
from (select distinct stdsubject from #student) as a
select @sql=@sql +'from #student group by stdname'
print @sql
exec(@sql)
逆转如下：
DROP table #student2
CREATE TABLE #student2 (stdname nvarchar(10),化学 int,数学 int,物理 int ,语文 int )
INSERT INTO #student2 VALUES ('李四',164,92,82,85)
INSERT INTO #student2 VALUES ('张三',0,90,85,80)
SELECT * FROM #student2
SELECT'李四'as stdname,stdsubject='化学', 化学 as result from #student2 where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='数学', 数学 as result from #student2 where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='物理', 物理 as result from #student2 where stdname='李四'
union all
SELECT'李四'as stdname,stdsubject='语文', 语文 as result from #student2 where stdname='李四'
union all
SELECT'张三'as stdname,stdsubject='化学', 化学 as result from #student2 where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='数学', 数学 as result from #student2 where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='物理', 物理 as result from #student2 where stdname='张三'
union all
SELECT'张三'as stdname,stdsubject='语文', 语文 as result from #student2 where stdname='张三'
declare @sql2 varchar(4000)
set @sql2 = ''
SELECT @sql2=@sql2+
'SELECT'''+stdname+'''as stdname,stdsubject=''化学'', 化学 as result from #student2 where stdname='''+stdname+'''
union all
SELECT'''+stdname+'''as stdname,stdsubject=''数学'', 数学 as result from #student2 where stdname='''+stdname+'''
union all
SELECT'''+stdname+'''as stdname,stdsubject=''物理'', 物理 as result from #student2 where stdname='''+stdname+'''
union all
SELECT'''+stdname+'''as stdname,stdsubject=''语文'', 语文 as result from #student2 where stdname='''+stdname+''' union all '
from (SELECT stdname FROM #student2) as a
SELECT @sql2 = LEFT(@sql2,LEN(@sql2) - 10)
PRINT(@sql2)
exec(@sql2)
select [name] into #tmpCloumns
from tempdb.dbo.syscolumns
where id=object_id('tempdb.dbo.#student2')
and [name]<>'stdname'
select * from #tmpCloumns
--查询表的列名select [name] from tempdb.dbo.syscolumns where id=object_id('tempdb.dbo.#student')
--char(10)+char(13) 《===》换行 和回车
declare @strSql nvarchar(800)
select @strSql=''
select @strSql=@strSql+'union all'+char(10)+char(13)+
'select [stdname],'''+[name]+''' as [科目],['+[name]+']'+char(10)+char(13)+
'from [#student2]'+char(10)+char(13)
from #tmpCloumns
print @strSql
select @strSql=substring(@strSql,11,len(@strSql))+'order by stdname,[科目]'
print @strSql
exec(@strsql)
------------------------------------------------------------------------------------------------------------------------------------------------------------
----
转 SQLServer 2005 实现交叉表格报表的利器 PIVOT 和 UNPIVOT 关系运算符
在SQLServer 2000环境中,如果要实现交叉表格报表,主要是靠一系列复杂的 SELECT...CASE 语句.
其实现过程请参阅这里T-SQL 交叉报表(行列互换) 交叉查询 旋转查询
在SQLServer 2005中我们可以使用PIVOT关系运算符来实现行列转换.
还是以学生成绩表来举例:
id姓名 科目 成绩
1 张三 语文 60
2 张三 数学 65
3 张三 外语 70
4 李四 语文 80
5 李四 数学 90
6 李四 外语 85
7 王五 语文 70
8 王五 数学 71
9 王五 外语 75
10 赵六 语文 64
11 赵六 数学 67
12 赵六 外语 76
查询后得出:
姓名 语文数学外语
李四 80 90 85
王五 70 71 75
张三 60 65 70
赵六 64 67 76
--准备数据:
select * from sysobjects where [xtype]='u'
go
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--删除与实验冲突的表
go
create table studentscore--创建实验表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
subject nvarchar(20) not null,
score int not null
)
go
select * from studentscore
go
--添加实验数据
insert studentscore values ('张三','语文','60');
insert studentscore values ('张三','数学','65');
insert studentscore values ('张三','外语','70');
insert studentscore values ('李四','语文','80');
insert studentscore values ('李四','数学','90');
insert studentscore values ('李四','外语','85');
insert studentscore values ('王五','语文','70');
insert studentscore values ('王五','数学','71');
insert studentscore values ('王五','外语','75');
insert studentscore values ('赵六','语文','64');
insert studentscore values ('赵六','数学','67');
insert studentscore values ('赵六','外语','76');
go
select * from studentscore
go
使用 SELECT...CASE 语句实现代码如下
select [name],
语文=max(case
when subject='语文' then score else 0
end),
数学=max(case
when subject='数学' then score else 0
end),
外语=max(case
when subject='外语' then score else 0
end)
from studentscore
group by [name]
结果:
下面我们使用PIVOT关系运算符来实现行列转换
select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'
from (select score,subject,[name] from studentscore) as ss
pivot
(
sum(score) for subject in([语文],[数学],[外语])
) as pvt
结果:用较少的代码完成了交叉表格报表
============================
对于这种方法要注意的一点是,我们使用sum()聚合函数,表面上没有指定按什么方式分组,但是自动按照name列分组了.
怎么做到的呢?原来pivot关系运算符会根据前面的对象中的列来自行判断,在这个例子中pivot前面的对象是ss,是个子查询,这个子查询中只有三列,score,subject和[name],但是
pivot运算符内部使用了score和subject这两列,那么肯定是对[name]分组.
所以我们得出,pivot运算符的分组规则是,跟随对象中的那些不在pivot运算符内部的列:
为了好理解我们再写一个例子:
--在ss子查询中，添加一列id，则pivot应该按照name和id分组
select [name],[语文] as '语文',[数学] as '数学',[外语] as '外语'
from (select score,subject,[name],id from studentscore) as ss
pivot
(
sum(score) for subject in([语文],[数学],[外语])
) as pvt
结果:验证了我们的设想
UNPIVOT关系运算符从字面上来看,就知道它的用途正好和PIVOT相反,下面举例说明:
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--删除与实验冲突的表
go
create table studentscore--创建实验表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
yuwen int not null,
shuxue int not null,
waiyu int not null
)
go
select * from studentscore
go
--添加实验数据
insert studentscore values ('张三','60','65','70');
insert studentscore values ('李四','80','90','86');
insert studentscore values ('王五','70','71','75');
insert studentscore values ('赵六','64','67','76');
go
select * from studentscore
go
结果:
SELECT id, [name],subject, score
FROM
(SELECT id,[name], 语文=yuwen, 数学=shuxue, 外语=waiyu
FROM studentscore) as ss
UNPIVOT
(score FOR subject IN
(语文, 数学, 外语)
)AS unpvt
结果:
