comma separated values

• NewBornDBA2017

  SSCarpal Tunnel

  Points: 4832

  More actions

  September 12, 2017 at 11:01 am

  #323499

  I have a table which contains values like FarmID + commodity. Example would be 
  ID      Commodity
  1          Apple
  1          Pear
  1          Strawberry
  2          Onion
  2          Carrots
  2          Orange

  And this is I want to accomplish
  ID     Commodity
  1       Apple, Pear, Strawberry
  2       Onion, Carrots, Orange

  Can someone help?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  September 12, 2017 at 11:18 am

  #1959159

  Try something along the lines of this:
  SELECT DISTINCT ID ,
        STUFF(( SELECT    ',' + Commodity
                FROM YourTable AS C2
                WHERE C2.ID = C1.ID
                ORDER BY Commodity
                FOR XML PATH('')
                ), 1, 1, '') as YourList
FROM YourTable AS C1;

  Sue

• NewBornDBA2017

  SSCarpal Tunnel

  Points: 4832

  More actions

  September 12, 2017 at 12:02 pm

  #1959165

  It worked. Thanks Sue.

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  September 12, 2017 at 12:11 pm

  #1959166

  newdba2017 - Tuesday, September 12, 2017 12:02 PM

  It worked. Thanks Sue.

  Now, do you understand how and why it works?  Do you also understand that if there are any XML reserved characters, such as "&", etc, that it's going to produce and entity code instead of the character?

  --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)
  

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 12, 2017 at 1:23 pm

  #1959174

  Expanding on Jeff's comments - this is one of those things that's worth understanding as FOR XML can be a complicated topic. Wayne Sheffield's article, Creating a comma-separated list (SQL Spackle), is a great resource.

  This probably won't be a problem if you are only dealing with alphabetical characters and spaces but will be for other characters. Note the difference in the following two result sets:

  declare @sometable table(ID int, Commodity varchar(100));
insert @sometable
values (1,'Apple'), (1,'Pear'), (1,'Strawberry'),
   (2,'Onion'), (2,'Carrots'), (2,'Orange'), (3,'<tomatillo>');

  SELECT DISTINCT ID ,
   STUFF(( SELECT  ',' + Commodity
      FROM @sometable AS C2
      WHERE C2.ID = C1.ID
      ORDER BY Commodity
      FOR XML PATH('')
      ), 1, 1, '') as YourList
FROM @sometable AS C1;

  SELECT DISTINCT ID ,
   STUFF(( SELECT  ',' + Commodity
      FROM @sometable AS C2
      WHERE C2.ID = C1.ID
      ORDER BY Commodity
      FOR XML PATH(''), TYPE).value('(text())[1]', 'varchar(100)') --as YourList, 1, 1, '')  
FROM @sometable AS C1;

  Once SQL Server 2017+ becomes the norm you'll be able to do this:
  SELECT ID , STRING_AGG(Commodity, ',')
FROM @sometable AS C1
GROUP BY ID;

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001