1.簡介
PIVOT通過將表達式某一列中的唯一值轉(zhuǎn)換為輸出中的多個列來旋轉(zhuǎn)表值表達式，并在必要時對最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT與PIVOT執(zhí)行相反的操作，將表值表達式的列轉(zhuǎn)換為列值。
通俗簡單的說：PIVOT就是行轉(zhuǎn)列，UNPIVOT就是列傳行
2.例題
--建立銷售表
CREATE TABLE Sell
(
[Year] INT,
[Quarter] NVARCHAR(10),
Quantity INT
)
--插入測試數(shù)據(jù)
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 行受影響)
--得到每年每季度的銷售總數(shù)
SELECT *
FROM Sell PIVOT ( SUM(Quantity) FOR [Quarter] IN ( Q1, Q2, Q3, Q4 ) ) AS P
GO
--查詢得如下結(jié)果
--注意：
--如果子組為空，SQL Server生成空值。如果聚合函數(shù)是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語句來實現(xiàn)，下面是sql2000的代碼：
--sql 2000 靜態(tài)版本
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 動態(tài)版本
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執(zhí)行幾乎完全相反的操作，將列轉(zhuǎn)換為行。
--創(chuàng)建測試表
CREATE TABLE TestUNPIVOT
(
ID INT,
A1 NVARCHAR(10),
A2 NVARCHAR(10),
A3 NVARCHAR(10)
)
--插入測試數(shù)據(jù)
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
--查詢得如下結(jié)果
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 實現(xiàn)語句：
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.總結(jié)
個人感覺PIVOT運算符相比SELECT...CASE語句就是代碼精簡了一些,似乎PIVOT可讀性好像不太好！
至少我看起來PIVOT語法有點怪怪，也許是還習慣吧！我個人還是喜歡用SELECT...CASE語句.
希望微軟能提供PIVOT運算符的動態(tài)版本，這樣動態(tài)生成列時，不用那么費事的累加字符串
=================================================================================
SQL 行列轉(zhuǎn)換 （PIVOT和UNPIVOT運算符 ）
http://www.cnblogs.com/aierong/archive/2008/09/03/1281777.html
1.簡介
PIVOT通過將表達式某一列中的唯一值轉(zhuǎn)換為輸出中的多個列來旋轉(zhuǎn)表值表達式，
并在必要時對最終輸出中所需的任何其余列值執(zhí)行聚合。
UNPIVOT與PIVOT執(zhí)行相反的操作，將表值表達式的列轉(zhuǎn)換為列值。
通俗簡單的說：PIVOT就是行轉(zhuǎn)列，UNPIVOT就是列傳行
2.例題
--建立銷售表
CREATE TABLE Sell
(
[Year] INT,
[Quarter] NVARCHAR(10),
Quantity INT
)
--插入測試數(shù)據(jù)
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 行受影響)
--得到每年每季度的銷售總數(shù)
SELECT *
FROM Sell PIVOT ( SUM(Quantity) FOR [Quarter] IN ( Q1, Q2, Q3, Q4 ) ) AS P
GO
--查詢得如下結(jié)果
--注意：
--如果子組為空，SQL Server生成空值。如果聚合函數(shù)是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語句來實現(xiàn)，下面是sql2000的代碼：
--sql 2000 靜態(tài)版本
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 動態(tài)版本
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執(zhí)行幾乎完全相反的操作，將列轉(zhuǎn)換為行。
--創(chuàng)建測試表
CREATE TABLE TestUNPIVOT
(
ID INT,
A1 NVARCHAR(10),
A2 NVARCHAR(10),
A3 NVARCHAR(10)
)
--插入測試數(shù)據(jù)
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
--查詢得如下結(jié)果
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 實現(xiàn)語句：
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.總結(jié)
個人感覺PIVOT運算符相比SELECT...CASE語句就是代碼精簡了一些,似乎PIVOT可讀性好像不太好！
至少我看起來PIVOT語法有點怪怪，也許是還習慣吧！我個人還是喜歡用SELECT...CASE語句.
希望微軟能提供PIVOT運算符的動態(tài)版本，這樣動態(tài)生成列時，不用那么費事的累加字符串
------------------------------------------------------------------------------------------------------------------------------------------------------------
----
在SQL Server 2005中，使用關(guān)鍵字PIVOT/UNPIVOT，可以很容易的實現(xiàn)行列轉(zhuǎn)換的需求?，F(xiàn)在將通過兩個簡單的例子詳細講解PIVOT/UNPIVOT的用法。
PIVOT的用法：
首先創(chuàng)建測試表，然后插入測試數(shù)據(jù) :
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將四個季度的利潤轉(zhuǎn)換成橫向顯示:
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的用法：
首先建立測試表，然后插入測試數(shù)據(jù):
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，將同一行中四個季度的列數(shù)據(jù)轉(zhuǎn)換成四行數(shù)據(jù)：
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)
今天看到有牛人用另一種方法實現(xiàn)了同樣的效果，轉(zhuǎn)來學習學習.
【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 ('張三','數(shù)學',90)
INSERT INTO #student VALUES ('張三','物理',85)
INSERT INTO #student VALUES ('李四','語文',85)
INSERT INTO #student values ('李四','數(shù)學',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 '數(shù)學' then result end),0) [數(shù)學]
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)
逆轉(zhuǎn)如下：
DROP table #student2
CREATE TABLE #student2 (stdname nvarchar(10),化學 int,數(shù)學 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='數(shù)學', 數(shù)學 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='數(shù)學', 數(shù)學 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=''數(shù)學'', 數(shù)學 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)
------------------------------------------------------------------------------------------------------------------------------------------------------------
----
轉(zhuǎn) SQLServer 2005 實現(xiàn)交叉表格報表的利器 PIVOT 和 UNPIVOT 關(guān)系運算符
在SQLServer 2000環(huán)境中,如果要實現(xiàn)交叉表格報表,主要是靠一系列復雜的 SELECT...CASE 語句.
其實現(xiàn)過程請參閱這里T-SQL 交叉報表(行列互換) 交叉查詢 旋轉(zhuǎn)查詢
在SQLServer 2005中我們可以使用PIVOT關(guān)系運算符來實現(xiàn)行列轉(zhuǎn)換.
還是以學生成績表來舉例:
id姓名 科目 成績
1 張三 語文 60
2 張三 數(shù)學 65
3 張三 外語 70
4 李四 語文 80
5 李四 數(shù)學 90
6 李四 外語 85
7 王五 語文 70
8 王五 數(shù)學 71
9 王五 外語 75
10 趙六 語文 64
11 趙六 數(shù)學 67
12 趙六 外語 76
查詢后得出:
姓名 語文數(shù)學外語
李四 80 90 85
王五 70 71 75
張三 60 65 70
趙六 64 67 76
--準備數(shù)據(jù):
select * from sysobjects where [xtype]='u'
go
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--刪除與實驗沖突的表
go
create table studentscore--創(chuàng)建實驗表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
subject nvarchar(20) not null,
score int not null
)
go
select * from studentscore
go
--添加實驗數(shù)據(jù)
insert studentscore values ('張三','語文','60');
insert studentscore values ('張三','數(shù)學','65');
insert studentscore values ('張三','外語','70');
insert studentscore values ('李四','語文','80');
insert studentscore values ('李四','數(shù)學','90');
insert studentscore values ('李四','外語','85');
insert studentscore values ('王五','語文','70');
insert studentscore values ('王五','數(shù)學','71');
insert studentscore values ('王五','外語','75');
insert studentscore values ('趙六','語文','64');
insert studentscore values ('趙六','數(shù)學','67');
insert studentscore values ('趙六','外語','76');
go
select * from studentscore
go
使用 SELECT...CASE 語句實現(xiàn)代碼如下
select [name],
語文=max(case
when subject='語文' then score else 0
end),
數(shù)學=max(case
when subject='數(shù)學' then score else 0
end),
外語=max(case
when subject='外語' then score else 0
end)
from studentscore
group by [name]
結(jié)果:
下面我們使用PIVOT關(guān)系運算符來實現(xiàn)行列轉(zhuǎn)換
select [name],[語文] as '語文',[數(shù)學] as '數(shù)學',[外語] as '外語'
from (select score,subject,[name] from studentscore) as ss
pivot
(
sum(score) for subject in([語文],[數(shù)學],[外語])
) as pvt
結(jié)果:用較少的代碼完成了交叉表格報表
============================
對于這種方法要注意的一點是,我們使用sum()聚合函數(shù),表面上沒有指定按什么方式分組,但是自動按照name列分組了.
怎么做到的呢?原來pivot關(guān)系運算符會根據(jù)前面的對象中的列來自行判斷,在這個例子中pivot前面的對象是ss,是個子查詢,這個子查詢中只有三列,score,subject和[name],但是
pivot運算符內(nèi)部使用了score和subject這兩列,那么肯定是對[name]分組.
所以我們得出,pivot運算符的分組規(guī)則是,跟隨對象中的那些不在pivot運算符內(nèi)部的列:
為了好理解我們再寫一個例子:
--在ss子查詢中，添加一列id，則pivot應該按照name和id分組
select [name],[語文] as '語文',[數(shù)學] as '數(shù)學',[外語] as '外語'
from (select score,subject,[name],id from studentscore) as ss
pivot
(
sum(score) for subject in([語文],[數(shù)學],[外語])
) as pvt
結(jié)果:驗證了我們的設想
UNPIVOT關(guān)系運算符從字面上來看,就知道它的用途正好和PIVOT相反,下面舉例說明:
if exists(select id from sysobjects where name='studentscore')
drop table studentscore--刪除與實驗沖突的表
go
create table studentscore--創(chuàng)建實驗表
(
[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
--添加實驗數(shù)據(jù)
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
結(jié)果:
SELECT id, [name],subject, score
FROM
(SELECT id,[name], 語文=yuwen, 數(shù)學=shuxue, 外語=waiyu
FROM studentscore) as ss
UNPIVOT
(score FOR subject IN
(語文, 數(shù)學, 外語)
)AS unpvt
結(jié)果:
