DB2多行结果合并到一行排列输出（类似递归）

需实现这样的功能，把同样接单量的人员姓名通过逗号分隔存到一个字段中

1 张三

1 李四

1 王五

处理后： 1  张三，李四，王五

 

-------------------------------------------分隔线----------------------------------------------

 

DW_RPT.TDM_ONLINESERVICE_CHAT_M

此表中数据如下：

 

 

处理后的结果如下：

 

 

WITH
CTE(CONN_CNT,OPER_NM,ORDER_CNT,ROWNUM)
AS
(
 SELECT
  CONN_CNT
  ,OPER_NM
  ,ORDER_CNT
  ,ROW_NUMBER() OVER (PARTITION BY CONN_CNT)
  FROM DW_RPT.TDM_ONLINESERVICE_CHAT_M
  ),
  CTE1(CONN_CNT,OPER_NM,ORDER_CNT,ROWNUM)
  AS
  (
   SELECT CONN_CNT,OPER_NM,ORDER_CNT,ROWNUM FROM CTE WHERE ROWNUM =1
   UNION ALL
   SELECT A.CONN_CNT,B.OPER_NM||','||A.OPER_NM,A.ORDER_CNT,B.ROWNUM+1 FROM CTE A,CTE1 B
   WHERE A.CONN_CNT = B.CONN_CNT AND A.ROWNUM = B.ROWNUM+1 
  )
  
  SELECT
	  DISTINCT D.CONN_CNT,
	  C.OPER_NM,
	  SUM(D.ORDER_CNT) ZDDS 
  FROM
  	DW_RPT.TDM_ONLINESERVICE_CHAT_M D
  INNER JOIN 
  (SELECT
  		A.CONN_CNT CONN_CNT,
  		A.OPER_NM OPER_NM 
  	FROM 
  		CTE1 A 
  	INNER JOIN
  		(SELECT 
  			CONN_CNT,
  			MAX(ROWNUM) ROWNUM 
  		FROM 
  			CTE1 
  		GROUP BY 
  		CONN_CNT) B
  ON A.CONN_CNT=B.CONN_CNT AND A.ROWNUM=B.ROWNUM) C 
  ON C.CONN_CNT=D.CONN_CNT  GROUP BY D.CONN_CNT,C.OPER_NM;

 

 

 

评论

1 楼 zl520878 2016-07-25