/*pivot 行轉列*/
/*
PIVOT (
AS
--顯示指定的元素(PIVOT運算符的圓括號中指定的元素)
-- 聚合函數(aggregate_func)
-- 聚合元素(aggregation_element)
-- 聚合函數的輸入必須是PIVOT運算符的輸入表中未處理的基列，不能是表達式
-- 如果想為聚合函數提供一個表達式作為輸入，可以現在派生表或CTE中進行處理，再讓PIVOT運算符對派生表或CTE進行操作
-- 擴展元素(spreading_element) (For關鍵字後面的列)
-- 透視轉換不能擴展多個列的屬性
-- 如果需要對多個列進行擴展，可以在表表達式中先進行聯接處理，再應用PIVOT運算符
-- 擴展值(target_col_list)
-- 與擴展元素對應的列值
--隱含的元素
-- 分組元素
-- 分組元素隱含出自那些沒有指定的屬性(PIVOT運算符的分組元素是輸入表的所有列中，既沒有指定為聚合元素，也沒有指定為擴展元素的那些列。)
*/
/*mss和ora在語法上的區別*/
/*初始化表結構*/
create table hp1 (id int,name varchar(10))
insert into hp1(id,name)
values(1,'a'),(1,'b'),(2,'a'),(3,'c'),(4,'b'),(4,'d'),(3,'1'),(3,'2');
/*sql server*/
with tbl as
( select * from
(select id,name,1 as col from hp1) t pivot(max(col) for name in ([a],[b]) ) p
)
select * from tbl where [a] = 1 and [b] = 1 ;
/*oracle11g*/
with tbl as
( select * from
(select id,name,1 as col from hp1) t pivot(max(col) for name in ('a','b') ) p
)
select * from tbl where "'a'" = 1 and "'b'" = 1 ;
/*應用舉例：統計每個客戶在2014年和2015年的消費金額*/
create table hpOrders
(OrderID int,
OrderDate date,
OrderYear varchar(4),
OrderMonth varchar(2),
CustomerID int,
TotalValue decimal(20,8)
);
/*Sql Server，準備數據*/
with t as
(
select top 1000 row_number() over(order by id) rn
from sysobjects
order by rn
)
insert into hpOrders(OrderID,OrderDate,OrderYear,OrderMonth,CustomerID,TotalValue)
select rn, dateadd(DAY,-1 * (rn%300), getdate()),
year(dateadd(DAY,-1 * (rn%300), getdate())),
right('0' + cast(month(dateadd(DAY,-1 * (rn%300), getdate())) as varchar(2)),2),
rn%10, rn
from t ;
/*應用舉例：統計每個客戶在2014年和2015年的消費金額*/
/*PIVOT: Sql Server*/
select * from
(select CustomerID,OrderYear,TotalValue from hpOrders) t
pivot(sum(TotalValue) for OrderYear in ([2014],[2015])) p ;
/*準備數據:oracle*/
insert into hpOrders(OrderID,OrderDate,OrderYear,OrderMonth,CustomerID,TotalValue)
select rownum, sysdate - mod(level,300),
to_char(sysdate - mod(level,300),'yyyy'),
to_char(sysdate - mod(level,300),'mm'),
mod(level,10), level
from dual
connect by level <= 1000;
/*應用舉例：統計每個客戶在2014年和2015年的消費金額*/
/*PIVOT: oracle11g*/
select * from
(select CustomerID,OrderYear,TotalValue from hpOrders) t
pivot(sum(TotalValue) for OrderYear in ('2014' as Y2014,'2015' as Y2015)) p ;
/*PIVOT: oracle10g*/
select CustomerID,
sum(decode(OrderYear,'2014',TotalValue,0)) as Y2014,
sum(decode(OrderYear,'2014',TotalValue,0)) as Y2015
from hpOrders
group by CustomerID;
/*逆透視 unpivot 列轉行
語法
UNPIVOT (
AS
-- UNPIVOT運算符比PIVOT運算符更簡單和直觀
-- UNPIVOT的第一個輸入是用於保存源表列值的目標列名稱
-- For關鍵字後面指定用於保存源表列名的目標列名稱
-- IN子句中的圓括號中指定要進行逆透視轉換的源表列名(
*/
/*為每個客戶和每個年份生成結果集中的一行，每行包括客戶id，訂單年份和訂貨量*/
/*Sql Server*/
select CustomerID,
coalesce([2014], 0) as [2014],
coalesce([2015], 0) as [2015]
into hpOrders1
from
(select CustomerID,OrderYear,TotalValue from hpOrders) t
pivot(sum(TotalValue) for OrderYear in ([2014],[2015])) p ;
select * from hpOrders1;
go
/*列轉行：使用cross join子句*/
select CustomerID, OrderYear, TotalValue
from (select CustomerID, OrderYear,
case OrderYear
when 2014 then [2014]
when 2015 then [2015]
end as TotalValue
from hpOrders1
cross join /*為各年度創建副本*/
(select 2014 as orderyear
union all select 2015) as orderyears
) as y
where TotalValue is not null;
/*列轉行：使用cross join+values子句*/
select CustomerID, OrderYear, TotalValue
from (select CustomerID, OrderYear,
case OrderYear
when 2014 then [2014]
when 2015 then [2015]
end as TotalValue
from hpOrders1
cross join /*sql server2008可將union all轉換為values子句，2005及之前的版本不支持該子句*/
(values(2014),(2015)) as orderyears(orderyear)) as y
where TotalValue is not null;
/*列轉行：unpivot*/
select CustomerID, OrderYear, TotalValue
from hpOrders1
unpivot(TotalValue for OrderYear in([2014],[2015])) as u;
go
drop table hpOrders1;
drop table hpOrders;
drop table hp1;
閱讀更多 老孔說編程 的文章
