oracle10g中行转列比较麻烦,需要写很多个decode,在11g中
使用以下语法就可以行转列、列转行,测试语句如下:
--行转列
with
a
as
(
select '4_20110417' ci,'A' ab,1 num from dual
union all
select '4_20110417','B',2 from dual
union all
select '5_20110417','A',1 from dual
union all
select '5_20110417','B',4 from dual
)
select *
from a
pivot
(
sum(num)
for ab in('A','B')
)
/*
4_20110417 1 2
5_20110417 1 4
*/
--列转行
with
a
as
(
select '4_20110417' ci,1 a ,2 b from dual
union all
select '5_20110417' ,1,4 from dual
)
select ci,bc,bb from a
unpivot
(
bb
for bc in (a,b)
)
/*
4_20110417 A 1
4_20110417 B 2
5_20110417 A 1
5_20110417 B 4
*/
使用以下语法就可以行转列、列转行,测试语句如下:
--行转列
with
a
as
(
select '4_20110417' ci,'A' ab,1 num from dual
union all
select '4_20110417','B',2 from dual
union all
select '5_20110417','A',1 from dual
union all
select '5_20110417','B',4 from dual
)
select *
from a
pivot
(
sum(num)
for ab in('A','B')
)
/*
4_20110417 1 2
5_20110417 1 4
*/
--列转行
with
a
as
(
select '4_20110417' ci,1 a ,2 b from dual
union all
select '5_20110417' ,1,4 from dual
)
select ci,bc,bb from a
unpivot
(
bb
for bc in (a,b)
)
/*
4_20110417 A 1
4_20110417 B 2
5_20110417 A 1
5_20110417 B 4
*/
with
a
as
(
select 'a' CITY1, 'b' CITY2 from dual
union all
select 'a' , 'c' from dual
)
a
as
(
select 'a' CITY1, 'b' CITY2 from dual
union all
select 'a' , 'c' from dual
)
SELECT CITY1, LISTAGG(a.CITY2,';') WITHIN GROUP (order by a.CITY2 desc) as CITY2_list
FROM a
GROUP BY a.CITY1;
FROM a
GROUP BY a.CITY1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-692841/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25323853/viewspace-692841/