How to combine the column values from the same table with condition in sql select query-CodePudding

I want to combine the Currency field by comparing Config and Product Column. If both field is repeated with duplicate values but different currency, the combine the currency into single row as you see in the screenshot.

I tried the code like

Select  DISTINCT LC.Config, LC.Product, CONCAT(LC.Currency,',',RC.Currency) as Currencies FROM [t_LimitCurrency] LC INNER JOIN [t_LimitCurrency] RC ON LC.[Config] = RC.[Config] AND LC.Product = RC.Product

Please let me know, how to write select statement for this scenario.

CodePudding user response：

Below Code should do the trick. I am using XML Path but you can use String_AGG in latest version of sql server

   select distinct Config,Product,
   STUFF((SELECT  ' ,'   CAST(Currency AS VARCHAR(max)) [text()]
   FROM (
   SELECT Currency
   FROM Yourtable b
  
   WHERE a.Config=b.Config and a.product=b.product
   ) ap
   FOR XML PATH(''), TYPE)
  .value('.','NVARCHAR(MAX)'),1,2,' ') Currency
   from Yourtable a