应用 Group By 与 sys_connect_by_path 实现字符连接

 select  mtl_lot.inventory_item_id,mtl_lot.organization_id,mtl_lot.locator_id
        ,ltrim( max(sys_connect_by_path(mtl_lot.LOT_NUMBER, ',') ),',') lot_number
        from
        (SELECT  moq.inventory_item_id
                ,moq.organization_id
                ,moq.locator_id
                ,moq.LOT_NUMBER
                , row_number() over(PARTITION BY moq.inventory_item_id,moq.organization_id,moq.locator_id 
                  ORDER BY moq.inventory_item_id,moq.organization_id,moq.locator_id ) rn
         FROM mtl_onhand_quantities moq,
              mtl_system_items_b msi,
              mtl_item_locations_kfv mil,
              org_organization_definitions ood,
              hr_operating_units hou
        WHERE moq.organization_id = msi.organization_id
          AND moq.inventory_item_id = msi.inventory_item_id
          AND moq.locator_id = mil.inventory_location_id(+)
          AND moq.organization_id = mil.organization_id(+)
          AND moq.organization_id = ood.organization_id
          AND ood.operating_unit = hou.organization_id
          and moq.SUBINVENTORY_CODE='LENDING'
        group by moq.inventory_item_id,moq.organization_id,moq.locator_id,LOT_NUMBER
        ) mtl_lot
        START WITH rn = 1
        CONNECT BY PRIOR rn = rn - 1
        AND mtl_lot.inventory_item_id = PRIOR mtl_lot.inventory_item_id
        AND mtl_lot.organization_id = PRIOR mtl_lot.organization_id
        AND mtl_lot.locator_id = PRIOR mtl_lot.locator_id
        group by  mtl_lot.inventory_item_id,mtl_lot.organization_id,mtl_lot.locator_id