例如: select a from table1;
column a 中的数据如下视图
abcdedf
ggeifkd
aaaaa
一个column 有三行字符串, 如何将这个column a 的三行字符串合并成一行字符串?变成 abcdedfggeifkdaaaaa 的一行?
column a 中的数据如下视图
abcdedf
ggeifkd
aaaaa
一个column 有三行字符串, 如何将这个column a 的三行字符串合并成一行字符串?变成 abcdedfggeifkdaaaaa 的一行?
CARD_CODE Q
--------- ------------------------------------------------
001 quarter_1
001 quarter_2
001 quarter_3
001 quarter_4
002 quarter_1
002 quarter_2
002 quarter_3
002 quarter_4数据格式二
CARD_CODE Q
--------- --------------------------------------------------------------------------------
002 quarter_1;quarter_2;quarter_3;quarter_4
001 quarter_1;quarter_2;quarter_3;quarter_4从格式一到格式二SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2)
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code
FROM (
SELECT 1 AS ID, ROW_NUMBER() OVER (ORDER BY a) AS curr
, ROW_NUMBER() OVER (ORDER BY a) -1 AS prev
FROM table1
)
START WITH curr = 1
CONNECT BY prev = PRIOR curr
GROUP BY ID
/
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(a,' ')),' ') AS LongStr
FROM (
SELECT 1 AS ID, a, ROW_NUMBER() OVER (ORDER BY a) AS curr
, ROW_NUMBER() OVER (ORDER BY a) -1 AS prev
FROM table1
)
START WITH curr = 1
CONNECT BY prev = PRIOR curr
GROUP BY ID
/