列转换为行

[jetsteven]

现有数据表如下：
组名称 值 排序
group1 S 1
group1 M 2
group1 L 3
group2 100 1
group2 110 2


想实现查询结果如下 ：
组名称 列1 列2 列3 ....
group1 S M L
group2 100 110 (null)

数据表的记录是不固定的。比如group2会有 120，130等等。

我在网上找来一下，怎么都实现不了我要的效果, 求高手解答。立马给分

[jdsnhan]

列数无法固定的时候，通过过程，动态获得列数，拼接行转列。

具体方法楼上的已经给了。

[samfeng_2003]

--建表
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);

[samfeng_2003]

--执行结果
组名称 列1 列2 列3 列4
---------------------------------
group1 S M L,
group2,100 110 120 130

[samfeng_2003]

--建表
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);

[IT_zei]

下面例子可以帮你解决：
with t1 as (
select row_number() over (partition by FCONTROLUNITID order by fname_l2) as parent,
row_number() over (partition by FCONTROLUNITID order by fname_l2)+1 as child,
FCONTROLUNITID ,fname_l2 from T_BD_Person
),
t2 (parent, child, FCONTROLUNITID, fname_l2, cnt) as (
select parent, child, FCONTROLUNITID, fname_l2, 1 from t1
where parent=1
union all
select t2.child, t2.child+1, t2.FCONTROLUNITID, rtrim(t2.fname_l2) || ', ' || rtrim(t1.fname_l2), cnt+1
from t2, t1
where t2.child=t1.parent
and cnt <=100
and t2.FCONTROLUNITID = t1.FCONTROLUNITID
)
select FCONTROLUNITID, fname_l2 from t2 where t2.child=(select max(child) from t1 where t2.FCONTROLUNITID=t1.FCONTROLUNITID)