oracle 行转列-WMSYS. wm_contact的运用

应用场景:主表与从表一对多的关系.一个患者有多个随访记录,我要直观的显示这个患者都有哪写随访记录.

sql:

SELECT WMSYS.WM_CONCAT(id) AS id ,

  CUSTOMER_MAIN_ID,

  MAX(CUSTOMER_DETAIL_ID)             AS CUSTOMER_DETAIL_ID ,

  TO_CHAR(START_TIME,'yyyy-mm-dd')    AS START_TIME ,

  WMSYS.WM_CONCAT(TO_CHAR(ITEM_NAME)) AS ITEM_NAME,

  MAX(customer_name)                  AS customer_name ,

  MAX(sex)                            AS sex,

  MAX(age)                            AS age,

  MAX(client_id)                      AS client_id,

  WMSYS.WM_CONCAT(type)               AS type,

  WMSYS.WM_CONCAT(item_id)            AS plan_item_id

FROM

  (SELECT pd.PLAN_DETAIL_ID AS id,

    pd.CUSTOMER_MAIN_ID ,

    pd.CUSTOMER_DETAIL_ID ,

    pd.plan_time AS START_TIME,

    pi.ITEM_NAME,

    pi.ITEM_ID,

    cm.customer_name,

    cm.sex,

    to_char(sysdate,'yyyy')-to_char(cm.BIRTHDAY,'yyyy')+decode(sign(to_char(sysdate,'mmdd')-to_char(cm.BIRTHDAY,'mmdd')),-1,-1,0,0,1,0) as age,

    cm.client_id,

    '1' AS type

  FROM HBPMS.HBP_PLAN_DETAILpd

  inner JOIN (select  hcm.customer_name,hcm.sex,hcm.HBP_CUSTOMER_MAIN_ID,hcm.BIRTHDAY,hcm.client_id from HBPMS.HBP_CUSTOMER_MAINhcm 

      left join HBPMS.sys_userssu on hcm.ORGANIZATION_ID=su.ORGANIZATION_ID

      left join HBPMS.sys_user_mappingsuma on su.user_id = suma.user_id

      where suma.O2O_DOCTOR_ID=#{doctorId})cm

  ON pd.CUSTOMER_MAIN_ID = cm.HBP_CUSTOMER_MAIN_ID

  LEFT JOIN HBPMS.DICT_PLAN_ITEM pi

  ON pd.PLAN_ITEM_ID  = pi.ITEM_ID

  WHERE pd.PLAN_STATUS=1 and<![CDATA[ pd.plan_time <= sysdate+5]]>

  UNION ALL

  SELECT ht.TASK_ID AS id,

    ht.CUSTOMER_MAIN_ID ,

    ht.CUSTOMER_DETAIL_ID ,

    ht.TASK_TIME AS START_TIME,

    pi.ITEM_NAME,

    pi.ITEM_ID,

    cm.customer_name,

    cm.sex,

    to_char(sysdate,'yyyy')-to_char(cm.BIRTHDAY,'yyyy')+decode(sign(to_char(sysdate,'mmdd')-to_char(cm.BIRTHDAY,'mmdd')),-1,-1,0,0,1,0) as age,

    cm.client_id,

    '2' AS type

  FROM HBPMS.HBP_TASKht

  inner JOIN (select  hcm.customer_name,hcm.sex,hcm.HBP_CUSTOMER_MAIN_ID,hcm.BIRTHDAY,hcm.client_id from HBPMS.HBP_CUSTOMER_MAINhcm 

      left join HBPMS.sys_userssu on hcm.ORGANIZATION_ID=su.ORGANIZATION_ID

      left join HBPMS.sys_user_mappingsuma on su.user_id = suma.user_id

      where suma.O2O_DOCTOR_ID=#{doctorId})cm

  ON ht.CUSTOMER_MAIN_ID = cm.HBP_CUSTOMER_MAIN_ID

  LEFT JOIN HBPMS.DICT_PLAN_ITEM pi

  ON ht.ITEM_ID    = pi.ITEM_ID

  WHERE TASK_STATUS=1

  ) t

GROUP BY t.CUSTOMER_MAIN_ID ,

  t.START_TIME

ORDER BY t.START_TIMEasc