Oracle 多行数据合并成一行数据

　　方法一

SQL> WITH SHOPPING AS
2  (
3      SELECT '1' U_ID, '苹果' GOODS, '2' QTY FROM DUAL UNION ALL
4      SELECT '2' U_ID, '梨子' GOODS, '5' QTY FROM DUAL UNION ALL
5      SELECT '1' U_ID, '西瓜' GOODS, '4' QTY FROM DUAL UNION ALL
6      SELECT '3' U_ID, '葡萄' GOODS, '1' QTY FROM DUAL UNION ALL
7      SELECT '3' U_ID, '香蕉' GOODS, '1' QTY FROM DUAL UNION ALL
8      SELECT '1' U_ID, '橘子' GOODS, '3' QTY FROM DUAL
9  )
10  SELECT U_ID, WMSYS.WM_CONCAT(GOODS) GOODS_SUM
11  FROM   SHOPPING
12  GROUP BY U_ID;
U_ID GOODS_SUM
---- --------------------------------------------------------------------------------
1    苹果,橘子,西瓜
2    梨子
3    葡萄,香蕉
　　方法二

SQL> WITH SHOPPING AS
2  (
3      SELECT '1' U_ID, '苹果' GOODS, '2' QTY FROM DUAL UNION ALL
4      SELECT '2' U_ID, '梨子' GOODS, '5' QTY FROM DUAL UNION ALL
5      SELECT '1' U_ID, '西瓜' GOODS, '4' QTY FROM DUAL UNION ALL
6      SELECT '3' U_ID, '葡萄' GOODS, '1' QTY FROM DUAL UNION ALL
7      SELECT '3' U_ID, '香蕉' GOODS, '1' QTY FROM DUAL UNION ALL
8      SELECT '1' U_ID, '橘子' GOODS, '3' QTY FROM DUAL
9  )
10  SELECT U_ID, SUBSTR(MAX(SYS_CONNECT_BY_PATH(GOODS, ',')), 2)  GOODS_SUM
11  FROM (
12      SELECT U_ID, GOODS, ROW_NUMBER() OVER(PARTITION BY U_ID ORDER BY U_ID) RN
13      FROM   SHOPPING
14  )
15  GROUP BY U_ID
16  START WITH RN = 1
17  CONNECT BY PRIOR RN = RN - 1 AND PRIOR U_ID = U_ID
18  ORDER BY U_ID;
U_ID GOODS_SUM
---- --------------------------------------------------------------------------------
1    葡萄,香蕉,橘子
2    梨子
3    葡萄,香蕉