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