3,494
社区成员




--建表
create table Tmp_tt
(
组名称 varchar2(20),
值 varchar2(10),
排序 number
);
--插入数据
insert into Tmp_tt
select 'group1','S',1 from dual
union all
select 'group1','M',2 from dual
union all
select 'group1','L',3 from dual
union all
select 'group2','100',1 from dual
union all
select 'group2','110',2 from dual
union all
select 'group2','120',3 from dual
union all
select 'group2','130',4 from dual;
--创建存储过程
create or replace procedure tmp_colToRow
(
data_set out SYS_REFCURSOR
)
AS
v_sql varchar2(5000);
v_tt varchar2(5000);
begin
v_sql := 'SELECT 组名称';
for x in (select 排序 from Tmp_tt group by 排序 order by 排序)
loop
v_tt := v_tt || ',MAX(decode(排序,'||to_char(x.排序)||',值,NULL)) 列'||to_char(x.排序);
end loop;
v_sql := v_sql || v_tt ||' from Tmp_tt group by 组名称 order by 组名称';
--DBMS_OUTPUT.PUT_LINE(v_sql);
open data_set for v_sql;
end;
--执行存储过程
exec tmp_colToRow(:v);
--执行结果
组名称 列1 列2 列3 列4
---------------------------------
group1 S M L,
group2,100 110 120 130
--建表
create table Tmp_tt
(
组名称 varchar2(20),
值 varchar2(10),
排序 number
);
--插入数据
insert into Tmp_tt
select 'group1','S',1 from dual
union all
select 'group1','M',2 from dual
union all
select 'group1','L',3 from dual
union all
select 'group2','100',1 from dual
union all
select 'group2','110',2 from dual
union all
select 'group2','120',3 from dual
union all
select 'group2','130',4 from dual;
--创建存储过程
create or replace procedure tmp_colToRow
(
data_set out SYS_REFCURSOR
)
AS
v_sql varchar2(5000);
v_tt varchar2(5000);
begin
v_sql := 'SELECT 组名称';
for x in (select 排序 from Tmp_tt group by 排序 order by 排序)
loop
v_tt := v_tt || ',MAX(decode(排序,'||to_char(x.排序)||',值,NULL)) 列'||to_char(x.排序);
end loop;
v_sql := v_sql || v_tt ||' from Tmp_tt group by 组名称 order by 组名称';
--DBMS_OUTPUT.PUT_LINE(v_sql);
open data_set for v_sql;
end;
--执行存储过程
exec tmp_colToRow(:v);