table为表名,id为相同字段,name为不同字段
select id, ltrim(sys_connect_by_path(name, ','), ',') name
from (select id,
name,
row_number() over(partition by id order by name) rn,
count(*) over(partition by id) cnt
from table) t
where rn = cnt
start with rn = 1
connect by prior id = id
and prior rn = rn - 1
order by id;
实例
select id,
STATE,
TASK_CODE,
TECHNOLOGU_ID,
TECHNOLOGU_CODE,
TECHNOLOGU_NAME,
CENTRE_ID,
CENTRE_CODE,
CENTRE_NAME,
PRIORITY,
TASK_ORDER,
PRODUCT_CODE,
PRODUCT_MODEL,
COUNT_SUM,
Production_Num,
productUnit,
SCHEDULING_DATE,
SCHEDULING_REMARKS,
CREATE_DATE,
EQUIPMENT_ID,
EQUIPMENT_CODE,
EQUIPMENT_NAME,
COMPLETE_DATE,
ltrim(sys_connect_by_path(CONTRACT_NUM, ','), ',') CONTRACT_NUM
from (select id,
STATE,
TASK_CODE,
TECHNOLOGU_ID,
TECHNOLOGU_CODE,
TECHNOLOGU_NAME,
CENTRE_ID,
CENTRE_CODE,
CENTRE_NAME,
PRIORITY,
TASK_ORDER,
PRODUCT_CODE,
PRODUCT_MODEL,
COUNT_SUM,
Production_Num,
productUnit,
SCHEDULING_DATE,
SCHEDULING_REMARKS,
CREATE_DATE,
EQUIPMENT_ID,
EQUIPMENT_CODE,
EQUIPMENT_NAME,
COMPLETE_DATE,
CONTRACT_NUM,
row_number() over(partition by id order by CONTRACT_NUM) rn,
count(*) over(partition by id) cnt
from (
SELECT A.ID,
PW.CONTRACT_NUM,
A.STATE,
A.TASK_CODE,
A.TECHNOLOGU_ID,
A.TECHNOLOGU_CODE,
A.TECHNOLOGU_NAME,
A.CENTRE_ID,
A.CENTRE_CODE,
A.CENTRE_NAME,
A.PRIORITY,
A.TASK_ORDER,
A.PRODUCT_CODE,
A.PRODUCT_MODEL,
A.COUNT_SUM,
NVL(SUM(B.PRODUCTION_NUM), 0) AS Production_Num,
C.EXT1 AS productUnit,
A.SCHEDULING_DATE,
A.SCHEDULING_REMARKS,
A.CREATE_DATE,
A.EQUIPMENT_ID,
A.EQUIPMENT_CODE,
A.EQUIPMENT_NAME,
A.COMPLETE_DATE
FROM PLAN_TASK A
LEFT JOIN Plan_Procedure_Info B
ON A.ID = B.TASK_ID
LEFT JOIN PLAN_PROCEDURE C
ON B.Procedure_Id = C.ID
LEFT JOIN PLAN_WORK PW
ON B.WORK_ID = PW.ID
LEFT JOIN fam_station D
ON A.CENTRE_ID = D.LINEID
WHERE 1=1 AND A.ID ='db0e3287-4280-49ff-93c2-93c95ca236c2'
GROUP BY A.ID,
PW.CONTRACT_NUM,
A.STATE,
A.TASK_CODE,
A.TECHNOLOGU_ID,
A.TECHNOLOGU_CODE,
A.TECHNOLOGU_NAME,
A.CENTRE_ID,
A.CENTRE_CODE,
A.CENTRE_NAME,
A.PRIORITY,
A.TASK_ORDER,
A.PRODUCT_CODE,
A.PRODUCT_MODEL,
A.COUNT_SUM,
C.EXT1,
A.SCHEDULING_DATE,
A.SCHEDULING_REMARKS,
A.CREATE_DATE,
A.EQUIPMENT_ID,
A.EQUIPMENT_CODE,
A.EQUIPMENT_NAME,
A.COMPLETE_DATE
ORDER BY A.COMPLETE_DATE ASC, A.PRIORITY, A.TASK_ORDER
)) t
where rn = cnt
start with rn = 1
connect by prior id = id
and prior rn = rn - 1
order by id;