CODEWITHSUNDEEP
sqlsql-serverduplicatesrowsno-duplicates
Hossein
Hossein

Reputation: 77

How can i merge duplicate rows in SQL with an special condition

i have a table with this structure

ID    Word         description
-------------------------------
1     book       a lot of paper
2     book       a thing
3     book       an amazing thing
4     tv         television

i want to convert this table to: 

ID    Word         description
-------------------------------
1     book       a lot of paper,a thing,an amazing thing
2     tv         television

Note that :

-The maximum number of duplication is six

-I want to have a table without duplicate rows , i want to merge all duplicate rows to one row with all description values.

Thanks for your helps...

Upvotes: 0

Views: 440

Answers (2)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You can try this:

SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Id,
    *
FROM (
    SELECT DISTINCT Word,
        STUFF((SELECT ',' + description
               FROM Your_Table
               WHERE Word = T.Word
               FOR XML PATH('')), 1, 1, '') AS description
    FROM Your_Table T) AS A

Upvotes: 3

davejal
davejal

Reputation: 6133

In your query u should group by word and concatenate by description

Tested this on mysql, not sure if it will work on sql server 

SELECT id, GROUP_CONCAT(description SEPARATOR ', ')
FROM tablename GROUP BY word

and for mssql server try the answer provided by @mdahlman

https://stackoverflow.com/a/106334/3664960

Upvotes: 0

Related Questions