sql写法之数据透视(行转列及列转行)

/*pivot 行转列*/

/*

<left_table_expression>

PIVOT (<aggregate_func(<aggregation_element>)> FOR

<spreading_element> IN(<target_col_list>))

AS <result_table_alias>

--显示指定的元素(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_table_expression>

UNPIVOT (<target_values_col> FOR

<target_names_col> IN(<source_col_list>))

AS <result_table_alias>

-- UNPIVOT运算符比PIVOT运算符更简单和直观

-- UNPIVOT的第一个输入是用于保存源表列值的目标列名称<target_values_col>

-- For关键字后面指定用于保存源表列名的目标列名称<target_names_col>

-- IN子句中的圆括号中指定要进行逆透视转换的源表列名(<source_col_list>) --比如比如([2006],[2007],[2008])

*/

/*为每个客户和每个年份生成结果集中的一行，每行包括客户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;