Ok i'm stumped...

• blue.4.ever

  Valued Member

  Points: 57

  More actions

  November 11, 2010 at 12:03 pm

  #231234

  Can this be done using a query?

  Here is your data

  ID Colors

  1 Blue

  1 Green

  1 Red

  2 Red

  3 Green

  3 Blue

  I need the following output

  ID Colors

  1 Blue, Green, Red

  2 Red

  3 Green, Blue

• Lamprey13

  SSCertifiable

  Points: 6210

  More actions

  November 11, 2010 at 12:13 pm

  #1249661

  You can, but I question if SQL is the right place to do it:DECLARE @T TABLE (ID INT, Colors VARCHAR(20))

  INSERT @T (ID, Colors) VALUES

  (1, 'Blue'),

  (1, 'Green'),

  (1, 'Red'),

  (2, 'Red'),

  (3, 'Green'),

  (3, 'Blue')

  SELECT DISTINCT

  T.ID,

  STUFF((SELECT DISTINCT TOP 100 PERCENT ', ' + T1.Colors FROM @T AS T1 WHERE T1.ID = T.ID ORDER BY ', ' + T1.Colors FOR XML PATH('')), 1, 1, '') AS Colors

  FROM

  @T AS T

  ORDER BY

  T.ID

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 11, 2010 at 12:13 pm

  #1249662

  nm

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  November 11, 2010 at 12:24 pm

  #1249671

  Using this article as a reference:

  http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/

  (Note, I searched here in the search box with the following: rows to delimited string)

  Please note how I adjusted your data setup. This makes it easily consumable for us:

  DECLARE @colors TABLE (cID INT, Colors VARCHAR(15))

  INSERT INTO (@colors)

  SELECT 1, 'Blue' UNION ALL

  SELECT 1, 'Green' UNION ALL

  SELECT 1, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 3, 'Green' UNION ALL

  SELECT 3, 'Blue'

  Drop this code into your window, it's commented to show you the different components.

  DECLARE @colors TABLE (cID INT, Colors VARCHAR(15))

  INSERT INTO @colors

  SELECT 1, 'Blue' UNION ALL

  SELECT 1, 'Green' UNION ALL

  SELECT 1, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 3, 'Green' UNION ALL

  SELECT 3, 'Blue'

  -- This builds the comma delimited list across the table.

  select c.Colors + ',' AS 'data()'

  FROM @colors AS c

  FOR XML PATH ('')

  -- This removes the linkage

  SELECT

  (select c.Colors + ',' AS 'data()'

  FROM @colors AS c

  FOR XML PATH ('')) AS ColorList

  --This appends a ,$ at the end, for the replace later.

  SELECT

  (select c.Colors + ',' AS 'data()'

  FROM @colors AS c

  FOR XML PATH ('')) + '$'

  --This shows you the results without the distinct

  select

  c2.cID,

  REPLACE( (select c.Colors + ',' AS 'data()'

  FROM @colors AS c

  WHERE c.cID = c2.cID

  FOR XML PATH ('')) + '$', ',$', '') AS ColorList

  FROM

  @colors AS c2

  --This is the final product

  select DISTINCT

  c2.cID,

  REPLACE( (select c.Colors + ',' AS 'data()'

  FROM @colors AS c

  WHERE c.cID = c2.cID

  FOR XML PATH ('')) + '$', ',$', '') AS ColorList

  FROM

  @colors AS c2

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• tmitchelar

  Hall of Fame

  Points: 3391

  More actions

  November 11, 2010 at 1:05 pm

  #1249690

  You could use the PIVOT relational operator to get the same result too.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  November 11, 2010 at 1:39 pm

  #1249717

  Using the table that Craig posted (modified only to add a PK to it), here are two solutions. The first may be faster due to not having a sort operation in the execution plan, but with this small sample data, you won't see the difference between them.

  DECLARE @colors TABLE (cID INT,

  Colors VARCHAR(15),

  PRIMARY KEY CLUSTERED (cID, Colors));

  INSERT INTO @colors

  SELECT 1, 'Blue' UNION ALL

  SELECT 1, 'Green' UNION ALL

  SELECT 1, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 3, 'Green' UNION ALL

  SELECT 3, 'Blue' ;

  WITH IDs AS

  (

  -- get distinct list of the id values

  SELECT DISTINCT cID

  FROM @colors

  )

  -- get the IDs from the above CTE

  SELECT IDs.cID,

  ca.CSV

  FROM IDs

  -- build comma-delimited strings for all colors of this ID.

  CROSS APPLY (SELECT CSV = stuff((SELECT ',' + Colors

  FROM @colors

  WHERE cID = IDs.cID

  FOR XML PATH(''),TYPE).value('.','varchar(1000)'),1,1,'')) ca;

  -- this also works, but adds a sort operation to the execution plan.

  SELECT DISTINCT

  cID,

  CsvTest = stuff((SELECT ',' + Colors

  FROM @colors

  WHERE cID = t1.cID

  FOR XML PATH(''),TYPE).value('.','varchar(1000)'),1,1,'')

  FROM @colors t1

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2