新手求助行转列问题

[tzbzll]

求助，工作中遇见一个问题：
表t1
id name
1 a
1 b
2 a
2 c
2 d
转成
表t2
id name
1 a,b
2 a,c,d
请大家指教啊！！！多谢
注意不能用sys_connect_by_path，因为在connect by 的时候由于层次太多了，有4000层，导致性能不能满足。
我原来的写法是：
select v.id,substr(MAX(sys_connect_by_path(v.name, '|')), 2) as name
from (select t1.id,row_number() over(PARTITION BY t1.id ORDER BY t1.name)rn from t1)v start with v.rn = 1 connect by v.id= PRIOR v.id
and v.rn - 1 = PRIOR v.rn GROUP BY v.id

[hebo2005]

最近问这个问题特别多噢,这种最好用存储过程或者函数来做,效率会高点

[hongqi162]

--测试数据
create table tbale1(no int, content varchar2(100));
insert into tbale1
select 1,'aa' from dual union all
select 1,'bb' from dual union all
select 1,'cc' from dual union all
select 2,'mm' from dual union all
select 2,'nn' from dual union all
select 3,'oo' from dual;
--建立函数
create or replace function sum_string(v_sql varchar2)
return varchar2
as
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
v_sum := v_sum||','||v_row;
end loop;
v_sum := substr(v_sum,2);
close l_alldata;
return v_sum;
end;
--执行查询
select distinct no,sum_string('select content from tbale1 where no='''||no||''' group by no,content') from tbale1
--执行结果
1 aa,bb,cc
2 mm,nn
3 oo

[hongqi162]

你参考一下上面的代码

[tzbzll]

感谢hongqi162(失踪的月亮)，他的方法基本是可以的，但是初步测试还存在两个问题：(1)解决数据重复问题,比如存在两条1 aa，怎么样只记录一次(2)由于数据我的数据量较大，效率不高，而且出现执行一半，pl/sql developer自动关闭了，不知道是怎么回事。自己再看看！

[tzbzll]

问题1已解决：用了这个函数INSTR(v_sum, v_row, -1, 1) = 0

[liuzi123]

关注下

[douyalove]

我也有发了一个这个问题,可还没解决啊

[dawugui]

和此例相同.
请教一条SQL 
NAME USERID
张三 KB001
张三 KB002
李四 KB001
李四 KB002
李四 KB003

我想用SQL实现表示如下:
NAME USERID
张三 KB001,KB002
李四 KB001,KB002,KB003



select cola,rtrim(
max(decode(colb,'KB001',colb||',',null))||
max(decode(colb,'KB002',colb||',',null))||
max(decode(colb,'KB003',colb||',',null)),',')
from test
group by cola
/

COLA RTRIM(MAX(DECODE(COLB,'KB001',COL
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB002






With x As (
 SELECT '张三' NAME, 'KB001' userid FROM dual UNION ALL
 SELECT '张三' NAME, 'KB002' userid FROM dual UNION ALL
 SELECT '李四' NAME, 'KB001' userid FROM dual UNION ALL
 SELECT '李四' NAME, 'KB002' userid FROM dual UNION ALL
 SELECT '王五' NAME, 'KB006' userid FROM dual UNION ALL
 SELECT '王五' NAME, 'KB002' userid FROM dual UNION ALL
 SELECT '李四' NAME, 'KB003' userid FROM dual)
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
  FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn, rownum prn FROM x)
 START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
 GROUP BY NAME
 ORDER BY NAME;

结果：
       NAME    USERID
1    李四    KB001,KB002
2    王五    KB002
3    张三    KB001,KB002
这里的“王五”信息不全。

with x AS (
 SELECT '张三' NAME, 'KB101' userid FROM dual UNION ALL
 SELECT '张三', 'KB002' FROM dual UNION ALL
 SELECT '张三', 'KB113' FROM dual UNION ALL
 SELECT '张三', 'KB003' FROM dual UNION ALL
 SELECT '张三', 'KB004' FROM dual UNION ALL
 SELECT '张三', 'KB005' FROM dual UNION ALL
 SELECT '张三', 'KB013' FROM dual UNION ALL
 SELECT '张三', 'KB103' FROM dual UNION ALL
 SELECT '李四', 'KB001' FROM dual UNION ALL
 SELECT '李四', 'KB002' FROM dual UNION ALL
 SELECT '王五', 'KB002' FROM dual UNION ALL
 SELECT '王五', 'KB006' FROM dual UNION ALL
 SELECT '李四', 'KB003' FROM dual)
--//--直接提取法
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid, ',')), ',') userid
  FROM (SELECT NAME, userid, row_number() over(PARTITION BY NAME ORDER BY userid) rn FROM x)
 START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND NAME = PRIOR NAME GROUP BY NAME ORDER BY NAME;