I have a table like
source dest1 dest2 -------------------- su1 du1 st1 su1 du1 st2 su1 du1 st3 su2 du1 st1 su2 du2 st4 su2 du2 st1
But my desired table is
source dest1 dest2 -------------------- su1 du1 st1 su1 du1 st2 su1 du1 st3 su2 (null) (null) su2 du2 st4 su2 du2 (null)
Rule here is Replacing dest2 by null if it appears once and Replacing dest1, dest2 by null if dest1 appears once…
Advertisement
Answer
You seem to be null’ing out dest2 after it appears once, and the pair when both appear. Assuming that source provides the ordering, you can do:
select source,
(case when row_number() over (partition by dest1, dest2 order by source) = 1
then dest1
end) as dest1,
(case when row_number() over (partition by dest2 order by source) = 1
then dest2
end) as dest2
from t;