CODEWITHSUNDEEP
sqlsql-server
Sid
Sid

Reputation: 141

Get unique comma separated values using group by in SQL

My table is as below

ID     | Name   | City 
------ | ------ | ------
1      |  ABC   | London, Paris 
1      |  ABC   | Paris
1      |  ABC   | Japan
2      |  XYZ   | Delhi
2      |  XYZ   | Delhi, New York

My output needs to be like this:

ID     | Name   | City 
------ | ------ | ------
1      |  ABC   | London, Paris, Japan
2      |  XYZ   | Delhi, New York

I see it as a 2 step process:

  1. Concatenate all the unique cities for each ID and Name. Example: For ID 1 and Name ABC Cities would be London, Paris, Japan

  2. Update the concatenated string by grouping the ID and Name.

I am able to do this for just one group, but how do I do this for all the different groups in the table?

Also, would cursors come into picture here when I want to update the string to all the rows matching the ID and Name.

Any help or idea on this would be appreciated.

Upvotes: 0

Views: 2688

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You should consider normalizing your table first.

Here, you first want to convert all the comma separated values into separate rows and then, group them together using STUFF and FOR XML PATH.

with your_table (ID, name, City)
as (
    select 1, 'ABC', 'London, Paris'
    union all
    select 1, 'ABC', 'Paris'
    union all
    select 1, 'ABC', 'Japan'
    union all
    select 2, 'XYZ', 'Delhi'
    union all
    select 2, 'XYZ', 'Delhi, New York'
    ), your_table_modified
as (
    select distinct id, name, Split.a.value('.', 'varchar(100)') City
    from (
        select id, name, cast('<x>' + replace(City, ', ', '</x><x>') + '</x>' as xml) as x
        from your_table
        ) t
    cross apply x.nodes('/x') as Split(a)
    )
select id, name, stuff((
            select ', ' + city
            from your_table_modified t2
            where t.id = t2.id
            for xml path(''), type
            ).value('(./text())[1]', 'varchar(max)')
            , 1, 2, '')
from your_table_modified t
group by id, name;

Produces:

enter image description here

Upvotes: 7

Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

Try this:

    select y.id,y.name,y.city from (
select id,name, case when Id=1 and name='ABC' then 'London,Paris,Japan'
                     when Id=2 and name='XYZ' then 'Delhi,Newyork'
                     end as CIty
        ,row_number () over (partition by id order by name asc) as rnk
from Yourtable
)y
where y.rnk=1

Upvotes: -1

Related Questions