例如: select a from table1;
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
------解决方案--------------------
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(a, ' ')), ' ') AS LongStr
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
/
------解决方案--------------------
试过了，楼上的方法正确。好像SQL SERVER 2000下没有该功能的语句支持吧，只能写存储函数。
------解决方案--------------------
在存储过程中游标循环出每条记录的值,然后定义字符串拼起来，哈哈