How do I combine more than one row of results based on a unique identifier

Question

I have the following table of data:

number  holiday
123456  LDN
123456  NYC
123456  IRL
456789  NYC
456789  CHILE

Basically each number can have up to 4 holidays; I need each number to only be displayed once, and then combine the holiday results into one field (rather than 3 in the example above for number 123456)

Ideally I want the table to display the following:

number  holiday
123456  LDN, NYC, IRL
456789  NYC, CHILE

I can either output the results to Excel and maniplaute from there, or use sql and temp tables.

Answer 1

If you are using MySQL then you can use GROUP_CONCAT:

SELECT number, GROUP_CONCAT(holiday)
FROM table1
GROUP BY number

If you are using SQL Server you can emulate GROUP_CONCAT by using the FOR XML PATH hack. See this article for more details.