简单行列转换.. with t1 as (select 'groupid1' as group_id,9999 as seach_code from dual union all select 'groupid2' as group_id,9999 as seach_code from dual union all select 'groupid3' as group_id,9999 as seach_code from dual), t2 as (select 'groupid1' as group_id,'groupkey11' as group_key from dual union all select 'groupid1' as group_id,'groupkey12' as group_key from dual union all select 'groupid1' as group_id,'groupkey13' as group_key from dual union all select 'groupid1' as group_id,'groupkey14' as group_key from dual union all select 'groupid2' as group_id,'groupkey21' as group_key from dual union all select 'groupid2' as group_id,'groupkey22' as group_key from dual union all select 'groupid3' as group_id,'groupkey31' as group_key from dual), t3 as (select 'groupkey11' as group_key,'a1' con1,'b1' con2,'c1' con3,'d1' con4,'e1' con5 from dual union all select 'groupkey12' as group_key,'a2' con1,'b2' con2,'' con3,'' con4,'' con5 from dual union all select 'groupkey13' as group_key,'a3' con1,'b3' con2,'c3' con3,'d3' con4,'' con5 from dual union all select 'groupkey14' as group_key,'a4' con1,'' con2,'' con3,'' con4,'' con5 from dual union all select 'groupkey21' as group_key,'a5' con1,'b5' con2,'c5' con3,'' con4,'' con5 from dual union all select 'groupkey22' as group_key,'a6' con1,'' con2,'' con3,'' con4,'' con5 from dual union all select 'groupkey31' as group_key,'a7' con1,'b7' con2,'' con3,'' con4,'' con5 from dual) SELECT GROUP_ID, MAX(DECODE(RN, 1, CON)) 条件1, MAX(DECODE(RN, 2, CON)) 条件2, MAX(DECODE(RN, 3, CON)) 条件3 FROM (SELECT A.GROUP_ID, A.RN, RTRIM(REGEXP_REPLACE(T3.CON1 || ',' || T3.CON2 || ',' || T3.CON3 || ',' || T3.CON4 || ',' || T3.CON5, '[,]+', ','), ',') AS CON FROM (SELECT T1.GROUP_ID, T2.GROUP_KEY, ROW_NUMBER() OVER(PARTITION BY T1.GROUP_ID ORDER BY T2.GROUP_KEY) RN FROM T1 LEFT JOIN T2 ON T2.GROUP_ID = T1.GROUP_ID) A LEFT JOIN T3 ON T3.GROUP_KEY = A.GROUP_KEY WHERE A.RN <= 3) GROUP BY GROUP_ID
with t1 as
(select 'groupid1' as group_id,9999 as seach_code from dual union all
select 'groupid2' as group_id,9999 as seach_code from dual union all
select 'groupid3' as group_id,9999 as seach_code from dual),
t2 as
(select 'groupid1' as group_id,'groupkey11' as group_key from dual union all
select 'groupid1' as group_id,'groupkey12' as group_key from dual union all
select 'groupid1' as group_id,'groupkey13' as group_key from dual union all
select 'groupid1' as group_id,'groupkey14' as group_key from dual union all
select 'groupid2' as group_id,'groupkey21' as group_key from dual union all
select 'groupid2' as group_id,'groupkey22' as group_key from dual union all
select 'groupid3' as group_id,'groupkey31' as group_key from dual),
t3 as
(select 'groupkey11' as group_key,'a1' con1,'b1' con2,'c1' con3,'d1' con4,'e1' con5 from dual union all
select 'groupkey12' as group_key,'a2' con1,'b2' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey13' as group_key,'a3' con1,'b3' con2,'c3' con3,'d3' con4,'' con5 from dual union all
select 'groupkey14' as group_key,'a4' con1,'' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey21' as group_key,'a5' con1,'b5' con2,'c5' con3,'' con4,'' con5 from dual union all
select 'groupkey22' as group_key,'a6' con1,'' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey31' as group_key,'a7' con1,'b7' con2,'' con3,'' con4,'' con5 from dual)
SELECT GROUP_ID, MAX(DECODE(RN, 1, CON)) 条件1, MAX(DECODE(RN, 2, CON)) 条件2,
MAX(DECODE(RN, 3, CON)) 条件3
FROM (SELECT A.GROUP_ID, A.RN,
RTRIM(REGEXP_REPLACE(T3.CON1 || ',' || T3.CON2 || ',' ||
T3.CON3 || ',' || T3.CON4 || ',' ||
T3.CON5, '[,]+', ','), ',') AS CON
FROM (SELECT T1.GROUP_ID, T2.GROUP_KEY,
ROW_NUMBER() OVER(PARTITION BY T1.GROUP_ID ORDER BY T2.GROUP_KEY) RN
FROM T1
LEFT JOIN T2
ON T2.GROUP_ID = T1.GROUP_ID) A
LEFT JOIN T3
ON T3.GROUP_KEY = A.GROUP_KEY
WHERE A.RN <= 3)
GROUP BY GROUP_ID