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

最新推荐文章于 2024-06-14 15:21:56 发布

iteye_9077

最新推荐文章于 2024-06-14 15:21:56 发布

阅读量468

点赞数

分类专栏： Oracle 文章标签： Oracle 行转列 WM_CONCAT SYS_CONNECT_BY_PATH

本文链接：https://blog.csdn.net/iteye_9077/article/details/82638196

版权

Oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

方法一

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    葡萄,香蕉