5

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?

I would like to combine all records in a certain field into a single cell (per value from another column) if the count of the records is more than 1. For example, if I have the following code

SELECT city, count(zoo name) AS 'count of zoo name' FROM mytable

It would generate the below results

enter image description here

The original table looks like this

enter image description here

Since both Atlanta and New York have more than one zoo and Tokyo only has one zoo, the final result should look like

enter image description here

How would I go about doing this? I thought about using the PIVOT construct, but that creates new columns for each possible value. I would also have to write the name of every possible zoo name into the PIVOT. This would be bad form since the actual data has much more possible values of "zoo name" than the above.

Community
  • 1
  • 1
Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248

1 Answers1

2

You can use XML Path to concat the column value 

;With Cte(city,concat) as
(
    select city, (select a.Zoo+','
    from Sample a
    where a.city=b.city
    for XML PATH ('') )  concat
    from Sample b
    group by city
 )
Select city,left(concat, len(concat) -1) from cte

Check the result in SQL Fiddle

praveen
  • 12,083
  • 1
  • 41
  • 49
  • I would trim this result to remove ending comma's. `LEFT(concat, LEN(concat)-1)` – Kermit Aug 09 '12 at 19:44
  • This winds up with an extra `,` at the end so you might want to wrap this in another select and add `left(concat, len(concat) -1)` to the concat field – Taryn Aug 09 '12 at 19:44
  • Thanks, that did the trick. Can you explain to me what "XML PATH ('') ) concat" is doing? – Lloyd Banks Aug 09 '12 at 20:25