表air有以下三个字段
id name memo
12 kk_123 备注
13 kk_426 备注2
12 kk_156 备注现要得到如下结果应该如何做呢?
id name memo
12 kk_123/kk_156 备注
13 kk_426 备注2
id name memo
12 kk_123 备注
13 kk_426 备注2
12 kk_156 备注现要得到如下结果应该如何做呢?
id name memo
12 kk_123/kk_156 备注
13 kk_426 备注2
调试欢乐多
from air
group by id,memo;
from air group by id, memo
select id, memo, ltrim(max(sys_connect_by_path(name, ',')), ',') from
(select row_number() over(partition by table1.id,table1.memo order by name) rn,table1.* from test1 table1)
start with rn = 1
connect by prior rn = rn - 1 and prior id = id
group by id, memo
order by id
LTRIM(MAX(SYS_CONNECT_BY_PATH(name,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
,memo
FROM (
SELECT id,memo,
, name
, ROW_NUMBER() OVER (PARTITION BY id,memo ORDER BY name) AS curr
, ROW_NUMBER() OVER (PARTITION BY id,memo ORDER BY name) -1 AS prev
FROM emp
)
GROUP BY id,memo
CONNECT BY prev = PRIOR curr AND id = PRIOR id
START WITH curr = 1;Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换列出
create table tc(a varchar2(5));
insert into tc values('a');
insert into tc values('b');
insert into tc values('c');
insert into tc values('d');
commit;SQL> select substr(max(sys_connect_by_path(a,',')),2) arr
2 from (
3 select a,row_number() over(order by a) rn
4 from tc
5 )
6 start with rn=1
7 connect by rn-1=prior rn;ARR
----------------------------------------------------------
a,b,c,d