Geek asks and answers

If count (values)> 1, combine all the values ​​in one cell

Possible duplicate:
SQL Server: can I rewrite multiple rows into one column?

I would like to combine all the records in a certain field into one cell (per value from another column) if the number of 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 will generate the results below.

enter image description here

The original table looks like this:

enter image description here

Since there are more than one zoo in Atlanta and New York, and there is only one zoo in Tokyo, the end result should look like

enter image description here

? PIVOT, . PIVOT. , " ", .

+5
1

XML-

;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

SQL Fiddle

+1

More articles:


All Articles