2

I have data in following format:

24  Asian Fish
24  Atlantis
24  Bakery
24  Bistro
24  Bon Appetite
24  Camo
24  Fish Bones
25  Black
38  Black
38  Burgundy
38  Dark Green
38  Navy
38  Red

Could you please suggest what query can be used to format it in following way:

24       Asian Fish|Atlantis|Bakery|Bistro|Bon Appetite|Camo|Fish Bones
25       Black
38       Black|Burgundy|Dark Green|Navy|Red

This is for SQL Server 2005.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Om.
  • 113
  • 14
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 14 '11 at 15:09
  • 4
    SQL databases **store** data - you shouldn't use them to do data visualization / presentation - that's the job of a UI component – marc_s Feb 14 '11 at 15:10
  • 1
    You need `group_concat` or similar. This will certainly be a dupe. Your other questions are SQL Server so probably of this one http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 – Martin Smith Feb 14 '11 at 15:10
  • how did you decide what to group on? I'm assuming there is some kind of category id somewhere. If you're using MySQL, check out `GROUP_CONCAT`. – dnagirl Feb 14 '11 at 15:12
  • @marc_s: There are millions of reasons why one could want to do this in the DB. I guess that's beyond this question... – Lukas Eder Feb 14 '11 at 15:14

1 Answers1

5

Since you didn't specify what database system you're using - here's one way to do it in SQL Server (2005 and up):

SELECT 
    DISTINCT ID,
    STUFF((SELECT '|' + t2.Fishy
     FROM dbo.YourTable t2
     WHERE t2.ID = t.ID
     FOR XML PATH('')), 1, 1, '') 'Fishes'
FROM dbo.YourTable t

This will produce the output:

ID  Fishes
24  Asian Fish|Atlantis|Bakery|Bistro|Bon Appetite|Camo|Fish Bones
25  Black
38  Black|Burgundy|Dark Green|Navy|Red
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459