/*pivot 行转列*/
/*
<left> /<left>
PIVOT (<aggregate>)> FOR/<aggregate>
<spreading> IN(<target>))/<target>/<spreading>
AS <result>
--显示指定的元素(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 列转行
语法
<left> /<left>
UNPIVOT (<target> FOR/<target>
<target> IN(<source>))/<source>/<target>
AS <result>
-- UNPIVOT运算符比PIVOT运算符更简单和直观
-- UNPIVOT的第一个输入是用于保存源表列值的目标列名称<target>
-- For关键字后面指定用于保存源表列名的目标列名称<target>
-- IN子句中的圆括号中指定要进行逆透视转换的源表列名(<source>) --比如比如([2006],[2007],[2008])/<source>
*/
/*为每个客户和每个年份生成结果集中的一行，每行包括客户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;
閱讀更多 老孔說編程 的文章
