您可以使用listagg将每个cntrc_key的所有行作为一行获取。将整行转换为几行将删除数据的含义。
我尝试了以下方法。

select 
   cntrc_key
   ,LISTAGG(distinct re_key, ',') WITHIN GROUP (ORDER BY re_key) as re_key
   ,LISTAGG(distinct AUP_KEY, ',') WITHIN GROUP (ORDER BY AUP_KEY) as AUP_KEY
   ,LISTAGG(distinct INSR_KEY, ',') WITHIN GROUP (ORDER BY INSR_KEY) as INSR_KEY
   ,LISTAGG(distinct DRVR_KEY, ',') WITHIN GROUP (ORDER BY DRVR_KEY) as DRVR_KEY
   ,LISTAGG(distinct OTHR_KEY, ',') WITHIN GROUP (ORDER BY OTHR_KEY) as OTHR_KEY
   ,LISTAGG(distinct LKP_KEY, ',') WITHIN GROUP (ORDER BY LKP_KEY) as LKP_KEY
from table_name
group by cntrc_key;

| CNTRC_KEY| RE_KEY| AUP_KEY| INSR_密钥|驱动器键|OTHR_键|LKP_KEY|
| --|--|--|--|--|--|--|
| 58046828 | 0 |0，45803863，45803957| 0.51021791| 0，58245215，58245216| 0.127270| 0 |
请让我知道如果这有帮助。或者让我知道你希望你的输出是如何的规则。

这里有一个选择;看看有没有用
第一部分（CTE束）用于区分行（row_number解析函数）; rn然后在“main”查询中用于连接。
最后一个where子句删除所有null值（coalesce函数）和所有0行（greatest函数）。

SQL> with
  2  t_cnt as
  3    (select cntrc_key, row_number() over (order by null) rn from test),
  4  t_re as
  5    (select cntrc_key, re_key  , row_number() over (order by re_key)   rn from (select distinct cntrc_key, re_key   from test)),
  6  t_aup as
  7    (select cntrc_key, aup_key , row_number() over (order by aup_key)  rn from (select distinct cntrc_key, aup_key  from test)),
  8  t_insr as
  9    (select cntrc_key, insr_key, row_number() over (order by insr_key) rn from (select distinct cntrc_key, insr_key from test)),
 10  t_drvr as
 11    (select cntrc_key, drvr_key, row_number() over (order by drvr_key) rn from (select distinct cntrc_key, drvr_key from test)),
 12  t_othr as
 13    (select cntrc_key, othr_key, row_number() over (order by othr_key) rn from (select distinct cntrc_key, othr_key from test)),
 14  t_lkp as
 15    (select cntrc_key, lkp_key , row_number() over (order by lkp_key)  rn from (select distinct cntrc_key, lkp_key  from test)),
 16  --
 17  temp as
 18    (select distinct c.cntrc_key, nvl(re_key, 0)   re_key  , nvl(aup_key, 0)  aup_key,
 19                                  nvl(insr_key, 0) insr_key, nvl(drvr_key, 0) drvr_key,
 20                                  nvl(lkp_key, 0)  lkp_key
 21     from t_cnt c left join t_re   r on r.cntrc_key = c.cntrc_key and r.rn = c.rn
 22                  left join t_aup  a on a.cntrc_key = c.cntrc_key and a.rn = c.rn
 23                  left join t_insr i on i.cntrc_key = c.cntrc_key and i.rn = c.rn
 24                  left join t_drvr d on d.cntrc_key = c.cntrc_key and d.rn = c.rn
 25                  left join t_othr o on o.cntrc_key = c.cntrc_key and o.rn = c.rn
 26                  left join t_lkp  l on l.cntrc_key = c.cntrc_key and l.rn = c.rn
 27    )
 28  select cntrc_key, re_key, aup_key, insr_key, drvr_key, lkp_key
 29  from temp
 30  where coalesce(re_key, aup_key, insr_key, drvr_key, lkp_key) is not null
 31    and greatest(re_key, aup_key, insr_key, drvr_key, lkp_key) > 0;

测试结果：

CNTRC_KEY     RE_KEY    AUP_KEY   INSR_KEY   DRVR_KEY    LKP_KEY
---------- ---------- ---------- ---------- ---------- ----------
  58046828          0   45803957          0   58245216          0
  58046828          0   45803863   51021791   58245215          0

SQL>

反枢轴+枢轴

with tt as
(
select distinct *
from t
unpivot (x for type in (cntrc_key,re_key,aup_key,insr_key,drvr_key,othr_key,lkp_key))
)
select *
from (select tt.*, row_number() over (partition by type order by x desc) rn from tt)
pivot (min(x) for type in ('CNTRC_KEY','RE_KEY','AUP_KEY','INSR_KEY','DRVR_KEY','OTHR_KEY','LKP_KEY'));

        RN 'CNTRC_KEY'   'RE_KEY'  'AUP_KEY' 'INSR_KEY' 'DRVR_KEY' 'OTHR_KEY'  'LKP_KEY'
---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
         1    58046828          0   45803957   51021791   58245216     127270          0
         2                          45803863          0   58245215          0           
         3                                 0                     0                      

3 rows selected.

。或者只是为了好玩

select cntrc_key,re_key,aup_key,insr_key,drvr_key,othr_key,lkp_key
from
(
select *
from t
model
unique single reference
dimension by 
(
dense_rank() over (order by cntrc_key desc) rn1,
dense_rank() over (order by re_key desc) rn2,
dense_rank() over (order by aup_key desc) rn3,
dense_rank() over (order by insr_key desc) rn4,
dense_rank() over (order by drvr_key desc) rn5,
dense_rank() over (order by othr_key desc) rn6,
dense_rank() over (order by lkp_key desc) rn7,
rownum rn
)
measures
(
cntrc_key,re_key,aup_key,insr_key,drvr_key,othr_key,lkp_key
)
rules
(
   cntrc_key[any,any,any,any,any,any,any,any] order by rn =
    nvl(min(cntrc_key)[cv(rn),any,any,any,any,any,any,any],0),
   re_key[any,any,any,any,any,any,any,any] order by rn = 
       nvl(min(re_key)[any,cv(rn),any,any,any,any,any,any],0),  
   aup_key[any,any,any,any,any,any,any,any] order by rn = 
      nvl(min(aup_key)[any,any,cv(rn),any,any,any,any,any],0),
   insr_key[any,any,any,any,any,any,any,any] order by rn = 
     nvl(min(insr_key)[any,any,any,cv(rn),any,any,any,any],0),
   drvr_key[any,any,any,any,any,any,any,any] order by rn = 
     nvl(min(drvr_key)[any,any,any,any,cv(rn),any,any,any],0),
   othr_key[any,any,any,any,any,any,any,any] order by rn = 
     nvl(min(othr_key)[any,any,any,any,any,cv(rn),any,any],0),
   lkp_key[any,any,any,any,any,any,any,any] order by rn =  
      nvl(min(lkp_key)[any,any,any,any,any,any,cv(rn),any],0)             
)
)
where (cntrc_key+re_key+aup_key+insr_key+drvr_key+othr_key+lkp_key) > 0
order by rn;

这里有一个使用MODEL子句解决这类问题的例子，但是由于你没有给予任何关于你打算应用的规则的足够信息，结果并不完全是你上面显示的那样，但至少它给出了一个想法：

WITH data(cntrc_key, re_key, aup_key, insr_key, drvr_key, othr_key, lkp_key) 
AS (
    SELECT  58046828, 0, 0, 0, 0, 0, 0  FROM DUAL UNION ALL
    SELECT  58046828, 0, 0, 0, 0, 127270, 0  FROM DUAL UNION ALL
    SELECT  58046828, 0, 45803863, 0, 0, 127270, 0 FROM DUAL UNION ALL
    SELECT  58046828, 0, 45803957, 0, 58245216, 0, 0  FROM DUAL UNION ALL
    SELECT  58046828, 0, 0, 51021791, 0, 0, 0  FROM DUAL UNION ALL
    SELECT  58046828, 0, 45803863, 0, 58245215, 0, 0 FROM DUAL -- UNION ALL
),
pdata AS (
    SELECT d.*
    FROM (
        SELECT rownum AS rn, d.* FROM DATA d
    ) d
)
SELECT DISTINCT cntrc_key, re_key, aup_key, insr_key,
drvr_key, othr_key, lkp_key FROM pdata 
MODEL
    PARTITION BY (cntrc_key)
    DIMENSION BY (rn)
    MEASURES(
        rn AS drn, 
            re_key, 0 AS next_re_key,
            aup_key, 0 AS next_aup_key, 
            insr_key, 0 AS next_insr_key, 
            drvr_key, 0 AS next_drvr_key,
            othr_key, 0 AS next_othr_key,
            lkp_key, 0 AS next_lkp_key
    )
    RULES 
    (
        next_re_key[ANY] = MIN(CASE WHEN re_key <> 0 THEN drn END)[rn >= cv(rn)],
        re_key[ANY] = CASE WHEN re_key[cv()] = 0 THEN NVL(re_key[cv()-1],re_key[cv()]) ELSE re_key[cv()] END,
        
        next_aup_key[ANY] = MIN(CASE WHEN aup_key <> 0 THEN drn END)[rn >= cv(rn)],
        aup_key[ANY] = CASE WHEN aup_key[cv()] = 0 THEN NVL(aup_key[cv()-1],aup_key[cv()]) ELSE aup_key[cv()] END,
        
        next_insr_key[ANY] = MIN(CASE WHEN insr_key <> 0 THEN drn END)[rn >= cv(rn)],
        insr_key[ANY] = CASE WHEN insr_key[cv()] = 0 THEN NVL(insr_key[cv()-1],insr_key[cv()]) ELSE insr_key[cv()] END,
        
        next_drvr_key[ANY] = MIN(CASE WHEN drvr_key <> 0 THEN drn END)[rn >= cv(rn)],
        drvr_key[ANY] = CASE WHEN drvr_key[cv()] = 0 THEN NVL(drvr_key[cv()-1],drvr_key[cv()]) ELSE drvr_key[cv()] END,
        
        next_othr_key[ANY] = MIN(CASE WHEN othr_key <> 0 THEN drn END)[rn >= cv(rn)],
        othr_key[ANY] = CASE WHEN othr_key[cv()] = 0 THEN NVL(othr_key[cv()-1],othr_key[cv()]) ELSE othr_key[cv()] END,
        
        next_lkp_key[ANY] = MIN(CASE WHEN lkp_key <> 0 THEN drn END)[rn >= cv(rn)],
        lkp_key[ANY] = CASE WHEN lkp_key[cv()] = 0 THEN NVL(lkp_key[cv()-1],lkp_key[cv()]) ELSE lkp_key[cv()] END,
        
        
        re_key[ANY] = NVL(re_key[next_re_key[cv()]],NVL(re_key[cv()-1],0)),
        aup_key[ANY] = NVL(aup_key[next_aup_key[cv()]],NVL(aup_key[cv()-1],0)),
        insr_key[ANY] = NVL(insr_key[next_insr_key[cv()]],NVL(insr_key[cv()-1],0)),
        drvr_key[ANY] = NVL(drvr_key[next_drvr_key[cv()]],NVL(drvr_key[cv()-1],0)),
        othr_key[ANY] = NVL(othr_key[next_othr_key[cv()]],NVL(othr_key[cv()-1],0)),
        lkp_key[ANY] = NVL(lkp_key[next_lkp_key[cv()]],NVL(lkp_key[cv()-1],0))

    )
;

CNTRC_KEY|RE_KEY|AUP_KEY |INSR_KEY|DRVR_KEY|OTHR_KEY|LKP_KEY|
---------+------+--------+--------+--------+--------+-------+
 58046828|     0|45803863|51021791|58245216|  127270|      0|
 58046828|     0|45803957|51021791|58245216|  127270|      0|
 58046828|     0|45803863|51021791|58245215|  127270|      0|