效果:
》》》
pivot 行转列
create table CostList(
Cost_date varchar2(20), --消费日期
Cost_name varchar2(20), --消费内容
Cost_money number(8) --消费金额
);
--插入数据
declare
--日期
cursor CL_CD is
select regexp_substr('16日、17日、18日、19日','[^、]+',1, level) Cost_date from dual
connect by level <= 4;
--内容
cursor CL_CN is
select regexp_substr('三餐、出行、水果、淘宝','[^、]+',1, level) Cost_name from dual
connect by level <= 4;
begin
--循环插入
for CD in CL_CD loop
for CN in CL_CN loop
insert into CostList
select CD.Cost_date, CN.Cost_name, floor(dbms_random.value(100,500)) from dual;
end loop;
end loop;
commit;
end;
select * from CostList;
select *
from CostList pivot(
max(Cost_money) for Cost_name in ( --Cost_name 即要转成列的字段
'三餐' as 三餐, --max(Cost_money) 此处必须为聚合函数,
'出行' as 出行, --in () 对要转成列的每一个值指定一个列名
'水果' as 水果,
'淘宝' as 淘宝
)
)
where 1 = 1;
unpivot 列转行
create table A as
select *
from CostList pivot(
max(Cost_money) for Cost_name in ( --Cost_name 即要转成列的字段
'三餐' as 三餐, --max(Cost_money) 此处必须为聚合函数,
'出行' as 出行, --in () 对要转成列的每一个值指定一个列名
'水果' as 水果,
'淘宝' as 淘宝
)
)
where 1 = 1;
select Cost_date , Cost_name, Cost_money from A unpivot (Cost_money for Cost_name in (三餐,出行,水果,淘宝) )