Need to group and rearrange the data (pivot the data concatenate it)

• SQL noob

  Newbie

  Points: 6

  More actions

  December 17, 2011 at 6:20 pm

  #391152

  Hello.

  Can someone please show me an example of how I can accomplish this in SQL server 2008:

  My simple query:

  SELECT Name, Color

  from TableName

  Results:

  

  But I need the output to be like this:

  

  I greatly appreciate any help. Thanks!

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  December 18, 2011 at 2:06 am

  #1422887

  here is an example using 'FOR XML PATH'

  -- create some data

  with produce (id,fruit, varieties)

  as (

  SELECT 101,'Apple', '3' UNION ALL

  SELECT 101,'Banana', '2' UNION ALL

  SELECT 102,'Orange', '1' UNION ALL

  SELECT 103,'Melon' ,'2' UNION ALL

  SELECT 103,'Grape' ,'1' UNION ALL

  SELECT 104,'Apple' ,'1' UNION ALL

  SELECT 105,'Banana' ,'1' UNION ALL

  SELECT 105,'Kiwi' ,'1' UNION ALL

  SELECT 105,'Tangerine' ,'1' UNION ALL

  SELECT 106,'Mango' ,'3' UNION ALL

  SELECT 106,'Melon' ,'2'

  )

  --query as follows

  SELECT id,

  Stuff((SELECT ',' + fruit

  FROM produce p2

  WHERE p1.id = p2.id

  ORDER BY p2.fruit --- sort by Fruit name

  FOR XML PATH('')), 1, 1, ' ')

  FROM produce p1

  GROUP BY id

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day