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

Question

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...

Answer 1

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

Answer 2

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