1

I have a Sample table:

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13           | 3
Islamabad | 10           | 4

This is Second Table

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 10           | 3
Islamabad | 10           | 4

I want my Final table to look like

Region    | OpenServices | DFC
Karaci    | 14           | 4
Lahore    | 13 , 10      | 3
Islamabad | 10           | 4
1

I'll assume LUW and a recent one. There you can use the LISTAGG aggregate function:

with Sample(region, openservices, dfc) as ( 
    values ('Karaci','14','4')
         , ('Lahore','13','3')
         , ('Islamabad','10','4')
), Second (region, openservices, dfc) as ( 
    values ('Karaci','14','4')
         , ('Lahore','10','3')
         , ('Islamabad','10','4')
) 
select region, substr(listagg(openservices,','),1,10), dfc 
from (
    select region, openservices, dfc 
    from sample 
    union 
    select region, openservices, dfc 
    from second
) 
group by region, dfc

The union will eliminate duplicate rows and the we can use listagg to concatenate openservices for the group region, dfc. Here I assumed that dfc is functionally dependent of region (according to you sample data). Since the return type of LISTAGG is a rather long string, I trimmed it down using substr.

Result:

REGION    2          DFC
--------- ---------- ---
Islamabad 10         4  
Karaci    14         4  
Lahore    10,13      3 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.