0

I have a table with 3 different columns. I need to combine two column values with some special character like comma based on first column value.

There are many examples with similar query but I do not find one with GroupBy and Special Character.

Table looks like this:

Region                  CountryName  ProductID
Latin America/Caribbean BAHAMAS         21
Europe                  AZERBAIJAN      23
Europe                  AZERBAIJAN      24
Latin America/Caribbean BAHAMAS         21
Middle East/Africa      BAHRAIN         43
Europe                  BALI            21
Asia Pacific            BANGLADHESH     25
Asia Pacific            BANGLADHESH     256
Latin America/Caribbean BARBADOS        20      
Europe                  BELARUS         15
Europe                  BELGIUM         24

Expected result is:

Region                  CountryName                         
Latin America/Caribbean BAHAMAS:21,BARBADOS:20              
Europe                  AZERBAIJAN:23,AZERBAIJAN:24,BALI:21,BELARUS:15,BELGIUM:24 
Asia Pacific            BANGLADHESH:25,BANGLADHESH:256
Middle East/Africa      BAHRAIN:43

I could not figure out the query to achieve this. Anybody have an idea on this?

Tech Learner
  • 1,227
  • 6
  • 24
  • 59
  • Why does the row for BAHRAIN not include the ProductID? This type of thing has been asked and answered hundreds and hundreds of times around SO and the rest of the internet. You need to use STUFF and FOR XML. – Sean Lange Feb 20 '19 at 15:02
  • @SeanLange - I have updated the question. I could see many examples but with Group by & special character I do not find one. – Tech Learner Feb 20 '19 at 15:07
  • Each of your elements is simply CountryName + ':' + ProductID. Then just follow the standard STUFF and FOR XML. – Sean Lange Feb 20 '19 at 15:15
  • Use this query.......... – DarkRob Feb 21 '19 at 11:08

0 Answers0