NAME COST
---- -----
ABC 101
HJK 130
HJK 206
KLM
OPU 105
OPU 109
OPU 113
OPU 117
OPU 118
OPU 199
OPU 287
OPU 288
XYZ 100
XYZ 101
XYZ 102
XYZ 203
XYZ 204
WITH N(COL,CNT,NAME,COST) AS (
SELECT 0 COL,COUNT(*) CNT,NAME,CAST('' AS VARCHAR(1000))
FROM ORION2
WHERE COST > ''
GROUP BY NAME
UNION ALL
SELECT N.COL + 1,N.CNT,PLUS.NAME,PLUS.COST || ',' || N.COST
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY NAME) COL,
NAME,COST
FROM ORION2
WHERE COST > ''
ORDER BY NAME,COST DESC) PLUS,N
WHERE PLUS.NAME = N.NAME
AND N.COL + 1 = PLUS.COL
AND N.COL + 1 <= N.CNT)
SELECT NAME,SUBSTR(RTRIM(COST),1,LENGTH(RTRIM(COST))-1)
FROM N
WHERE COL = CNT
UNION
SELECT NAME,COST FROM ORION2 WHERE COST = ''
ORDER BY NAME;
/*結果如下:
NAME COST
---- -------------------------------
ABC 101
HJK 130,206
KLM
OPU 105,109,113,117,118,199,287,288
XYZ 100,101,102,203,204
