CODEWITHSUNDEEP
sqlsql-server
Md Salim
Md Salim

Reputation: 51

How to handle duplicate row with join

I have a table bic_table.

------------------------- 
KeyInstn  | SwiftBICCode  
100369    |  BOFAUSV1     
100369    |  MLCOUS33

keyInstn_table

------------------------- 
KeyInstn  | country  
100369    | USA
100370    | India

I am trying to join keyInstn_table with bic_table.

And I want to join both value as a comma separated.

How to get the result as

------------------------- 
KeyInstn  | country  | SwiftBICCode   
100369    | USA      | BOFAUSV1,MLCOUS33
100370    | India    | BOFH76HG
-------------------------

Upvotes: 0

Views: 72

Answers (1)

Fahmi
Fahmi

Reputation: 37483

If your database version is SQL Server 2017+ then you can use following:

SELECT a.keyInstn, country,STRING_AGG(SwiftBICCode, ', ') AS SwiftBICCode
FROM tablename a inner join keyInstn_table b on a.keyInstn=b.KeyInstn
GROUP BY a.keyInstn,country

Alternatively, you can use stuff() for lower versions of SQL Server

select u.keyInstn, country, 
stuff(( select concat( ',', SwiftBICCode) from tablename y 
        where y.keyInstn= u.keyInstn for xml path('')),1,1, '') 
from tablename u inner join keyInstn_table b on u.keyInstn=b.KeyInstn
group by u.keyInstn,country

Upvotes: 1

Related Questions