I have a table like below :

Required Output :

The code which I am trying to get the resultant output:

with result AS (
SELECT 
DISTINCT
P.col_1,
    P.col_2 ,
    CASE WHEN TRIM(P.col_1)=TRIM(P.col_2) THEN 'NULL'
   WHEN  trim(P.col_1) != trim(P.col_2) and P.col_1 NOT IN (SELECT DISTINCT s.col_2 FROM 
   sample s) 
   THEN   LISTAGG(DISTINCT SUBSTR(col_1,1,2)  ,', ') OVER(PARTITION BY P.col_2) 
   END   result_col
   FROM  table P )select * from result where i_part='6817'

But I am not getting the desired results can anyone suggest where I am missing

CodePudding user response：

You can re-write the query as below -

with data_cte(col_1, col_2) as
(
select * from values 
('AB12','6817'),
('CD13','6817'),
('6817','6817'),
('WL34','5412'),
('XY23','5412')
), cte_1 as
(
select col_1,listagg(SUBSTR(col_1,1,2),',') over (partition by col_2) col_2 from data_cte 
where col_1 <> col_2)
select d1.col_1, d1.col_2, 
case when d1.col_1 = d1.col_2 then 'NULL'
else d2.col_2 end result_col 
from data_cte d1
left join cte_1 d2
on d1.col_1 = d2.col_1 ;

To get following result -