oracle wm_concat函数简单实例（实现行转成用分隔符分隔的一列）

主从表关联查询出从表某个字段的值用逗号连接起来

主表：故障单表 SP_PD_FAULT

从表：报障单表 SP_PD_FAULT_REPORT

目的：通过故障单关联查询出该故障单的报障单号，且用逗号分隔显示为一列

SELECT A.*, B.FAULT_NOS, B.FAULT_REPORT_IDS
  FROM (SELECT *
          FROM (SELECT DISTINCT (F.ID) F_ID,
                                F.*,
                                O.FAULT_OCCUR_TIME,
                                O.FAULT_ADDRESS,
                                O.FAULT_DESC
                  FROM LCAM_SC.SP_PD_FAULT F,
                       (SELECT A.FAULT_ID,
                               A.FEEDER_ID,
                               A.PROVINCE_CODE,
                               A.FAULT_REPORT_STATUS,
                               A.FAULT_REPORT_FROM,
                               A.FAULT_REPORT_NO,
                               A.FAULT_ADDRESS,
                               A.FEEDER_NAME,
                               A.FAULT_REPORT_STATUS,
                               A.SUBSTATION_ID,
                               A.SUBSTATION_NAME,
                               A.ORGANIZATION_ID,
                               A.TEAM_ID,
                               A.BUREAU_CODE,
                               A.CS_WORKORDER_NO,
                               A.FAULT_DESC,
                               A.FAULT_REPORTER,
                               B.FAULT_OCCUR_TIME
                          FROM LCAM_SC.SP_PD_FAULT_REPORT A,
                               (SELECT FAULT_ID,
                                       MIN(FAULT_OCCUR_TIME) FAULT_OCCUR_TIME
                                  FROM LCAM_SC.SP_PD_FAULT_REPORT
                                 GROUP BY FAULT_ID) B
                         WHERE A.FAULT_ID = B.FAULT_ID
                           AND (A.IS_MERGE = '1' OR A.IS_MERGE IS NULL)
                           AND A.PROVINCE_CODE = '03'
                           AND B.FAULT_OCCUR_TIME BETWEEN
                               TO_DATE('2016-01-01 00:00',
                                       'yyyy-mm-dd hh24:mi') AND
                               (TO_DATE('2017-08-02 00:00',
                                        'yyyy-mm-dd hh24:mi'))) O
                 WHERE F.ID = O.FAULT_ID
                   AND F.PROVINCE_CODE IS NOT NULL
                   AND F.BUREAU_CODE IS NOT NULL
                   AND O.PROVINCE_CODE = '03'
                   AND F.FAULT_STATUS IN (2, 3, 4, 5, 6, 7)
                   AND O.FAULT_OCCUR_TIME BETWEEN
                       TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND
                       TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi'))) A,
       (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO)) FAULT_NOS,
               TO_CHAR(WM_CONCAT(ID)) FAULT_REPORT_IDS,
               FAULT_ID
          FROM LCAM_SC.SP_PD_FAULT_REPORT
         GROUP BY FAULT_ID) B
 WHERE A.ID = B.FAULT_ID(+)

从表使用GROUP BY外键字段，通过WM_CONCAT函数把报账单号和ID连接成一个字符串

       (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO)) FAULT_NOS,
               TO_CHAR(WM_CONCAT(ID)) FAULT_REPORT_IDS,
               FAULT_ID
          FROM LCAM_SC.SP_PD_FAULT_REPORT
         GROUP BY FAULT_ID) B
 WHERE A.ID = B.FAULT_ID(+)

但是：报错 不存在的LOB值

出现这个错误很多时候是使用了wmsys.wm_concat的同时使用了group ,distinct 或者union，本来两者没有问题，问题在于：

1.Oracle Database 10g Enterprise Edition Release 10.2.0.5.0以后的版本wmsys.wm_concat查询出的是LOB类型

2.oralce的SQL语句中若查询了LOB字段是不能使用distinct，union，和group by等关键字的。

改成在把关联查询写成子查询

SELECT A.*,
       (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO))
          FROM LCAM_SC.SP_PD_FAULT_REPORT B
         WHERE A.ID = B.FAULT_ID) FAULT_NOS,
       (SELECT TO_CHAR(WM_CONCAT(ID))
          FROM LCAM_SC.SP_PD_FAULT_REPORT B
         WHERE A.ID = B.FAULT_ID) FAULT_REPORT_IDS
  FROM (SELECT DISTINCT (F.ID) F_ID,
                                F.*,
                                O.FAULT_OCCUR_TIME,
                                O.FAULT_ADDRESS,
                                O.FAULT_DESC
                  FROM LCAM_SC.SP_PD_FAULT F,
                       (SELECT A.FAULT_ID,
                               A.FEEDER_ID,
                               A.PROVINCE_CODE,
                               A.FAULT_REPORT_STATUS,
                               A.FAULT_REPORT_FROM,
                               A.FAULT_REPORT_NO,
                               A.FAULT_ADDRESS,
                               A.FEEDER_NAME,
                               A.FAULT_REPORT_STATUS,
                               A.SUBSTATION_ID,
                               A.SUBSTATION_NAME,
                               A.ORGANIZATION_ID,
                               A.TEAM_ID,
                               A.BUREAU_CODE,
                               A.CS_WORKORDER_NO,
                               A.FAULT_DESC,
                               A.FAULT_REPORTER,
                               B.FAULT_OCCUR_TIME
                          FROM LCAM_SC.SP_PD_FAULT_REPORT A,
                               (SELECT FAULT_ID,
                                       MIN(FAULT_OCCUR_TIME) FAULT_OCCUR_TIME
                                  FROM LCAM_SC.SP_PD_FAULT_REPORT
                                 GROUP BY FAULT_ID) B
                         WHERE A.FAULT_ID = B.FAULT_ID
                           AND (A.IS_MERGE = '1' OR A.IS_MERGE IS NULL)
                           AND A.PROVINCE_CODE = '03'
                           AND B.FAULT_OCCUR_TIME BETWEEN
                               TO_DATE('2016-01-01 00:00',
                                       'yyyy-mm-dd hh24:mi') AND
                               (TO_DATE('2017-08-02 00:00',
                                        'yyyy-mm-dd hh24:mi'))) O
                 WHERE F.ID = O.FAULT_ID
                   AND F.PROVINCE_CODE IS NOT NULL
                   AND F.BUREAU_CODE IS NOT NULL
                   AND O.PROVINCE_CODE = '03'
                   AND F.FAULT_STATUS IN (2, 3, 4, 5, 6, 7)
                   AND O.FAULT_OCCUR_TIME BETWEEN
                       TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND
                       TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi')) A

外面包一层

SELECT A.*,
       (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO))
          FROM LCAM_SC.SP_PD_FAULT_REPORT B
         WHERE A.ID = B.FAULT_ID) FAULT_NOS,
       (SELECT TO_CHAR(WM_CONCAT(ID))
          FROM LCAM_SC.SP_PD_FAULT_REPORT B
         WHERE A.ID = B.FAULT_ID) FAULT_REPORT_IDS
  FROM (

把需要的连接字符串使用子查询查出来