有这样一个表:
MANDT QMNUM FENUM URGRP URCOD KURZTEXT
1 800 001000000372 0002 0103 04 文本1
2 800 001000000372 0003 0104 08 文本2
3 800 001000000372 0001 0104 08 文本3我想把QMNUM相等的文本信息合并到一条记录里,原始表中QMNUM相等的记录行数不定,或许有两行或者多行。
QMNUM KURZTEXT
001000000372 文本1;文本2;文本3请问大家该如何用一条SQL语句实现?
MANDT QMNUM FENUM URGRP URCOD KURZTEXT
1 800 001000000372 0002 0103 04 文本1
2 800 001000000372 0003 0104 08 文本2
3 800 001000000372 0001 0104 08 文本3我想把QMNUM相等的文本信息合并到一条记录里,原始表中QMNUM相等的记录行数不定,或许有两行或者多行。
QMNUM KURZTEXT
001000000372 文本1;文本2;文本3请问大家该如何用一条SQL语句实现?
select max(substr((sys_connect_by_path(KURZTEXT,';')),2)) KURZTEXT
from (
select QMNUM,KURZTEXT,
rownum rnum,
row_number() over(partition by QMNUM order by QMNUM) rn1
from a
)
start with rn1=1
connect by rnum-1=prior rnum
group by QMNUM 试试这个
FROM
(SELECT QMNUM, KURZTEXT, row_number() over(PARTITION BY QMNUM ORDER BY QMNUM) rn FROM a)
START WITH rn = 1 CONNECT BY rn - 1 = PRIOR rn AND id = PRIOR id
GROUP BY QMNUM ORDER BY QMNUM;试试这个
from (select QMNUM,
KURZTEXT,
count(*) rnum,
row_number() over(partition by QMNUM order by QMNUM) rn1
from a09
group by qmnum, KURZTEXT)
start with rn1 = 1
connect by rnum - 1 = prior rnum
group by QMNUM如果group by中没有KURZTEXT就好了,但是通不过。
谢谢你,我再看看
建議你看哈這個博客http://jack198409.itpub.net/