get all the values in col2 mapping to col1 as comma seperated in the same row

• Alicia Rose

  Ten Centuries

  Points: 1044

  More actions

  November 6, 2007 at 1:46 pm

  #376740

  Hi sql gurus,

  I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .

  The tables below might give a better idea

  current scenario:

  Product Name SubmissionID columnC Column D

  AAA 123

  AAA 456

  BBB 111

  ccc 121

  AAA 789

  Expected result:

  Product NameSubmissionID columnC Column D

  AAA 123,456,789

  BBB 111

  CCC 121

  Alicia Rose

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  November 6, 2007 at 9:02 pm

  #748949

  Duplicate post...

  http://www.sqlservercentral.com/Forums/Topic419299-266-1.aspx

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• David McFarland

  SSChampion

  Points: 11815

  More actions

  November 8, 2007 at 10:59 am

  #749590

  I was going to respond in the other thread, but since it's in the Data Corruption forum for some reason, I'll toss it in here. If you'd rather not use a function, you can use the FOR XML Path method with an empty wrapping element, like (this example uses Jeff's table from the other thread, so modify to fit your needs):

  SELECT

  ProductName

  ,ConcatSubmissionIDs = Stuff(

  (

  SELECT

  ','+ Cast(SubmissionID AS varchar(10))

  FROM

  yourtable AS yt1

  WHERE

  yt1.ProductName = yt2.ProductName

  FOR XML PATH('')

  ),1,1,''

  )

  FROM

  yourtable AS yt2

  GROUP BY

  ProductName

  ORDER BY

  ProductName