CODEWITHSUNDEEP
sqlsql-server-2005
JoSav
JoSav

Reputation: 247

Sql Compare Comma Separated Value

I'm searching a way to compare a value in the same cell.

For now I have a data that i get with a query and the output is like:

ID|firstname|lastName|Gender|Tag
1 |Jo       |Sav     |b     |sport,computer,sport
2 |Franck   |Gag     |b     |climbing, swimming
3 |Oli      |Dann    |b     |cycling,running,cycling
4 |Alyss    |Baby    |g     |make up, cycling

All I want is to get only the person who get duplicates data in tag columns.

So the output will be like:

ID|firstname|lastName|Gender|Tag
1 |Jo       |Sav     |b     |sport,computer,sport
3 |Oli      |Dann    |b     |cycling,running,cycling

Thanks in advance!

Edit #1:For people want to know how my schema is.

ID|firstname|lastName|Gender|Tag
1 |Jo       |Sav     |b     |sport
2 |Franck   |Gag     |b     |climbing
3 |Oli      |Dann    |b     |cycling
4 |Alyss    |Baby    |g     |make up
1 |Jo       |Sav     |b     |computer
1 |Jo       |Sav     |b     |sport
2 |Franck   |Gag     |b     |swimming
3 |Oli      |Dann    |b     |cycling
3 |Oli      |Dann    |b     |running
...

Upvotes: 1

Views: 325

Answers (1)

John Woo
John Woo

Reputation: 263723

You can do it like this. The extra EXISTS clause checks if the ID is present in the records which has duplicate Tag.

SELECT  ID, firstname, lastName, Gender,
        STUFF((SELECT ',' + Tag
              FROM TableName
              WHERE ID = a.ID
              FOR XML PATH ('')) , 1, 1, '')  AS TagList
FROM    TableName AS a
WHERE   EXISTS
        (
            SELECT 1
            FROM tableName b
            WHERE b.ID = a.ID
            GROUP BY b.id, b.tag
            HAVING COUNT(*) > 1
        )
GROUP   BY ID, firstname, lastName, Gender

Upvotes: 3

Related Questions