Combine Multi Rows with COALESCE

Question

Using SQL-Server 2012

I have the following Table:

Id      Description 
6192    Salzburg
6193    Salzburg
6194    Salzburg
6196    Innsbruck
6197    Innsbruck
6198    Innsbruck
6199    Innsbruck
6201    Bregenz
6202    Bregenz
6203    Bregenz

I want to Select each Distinct "Description" with all the Id's together in one string:

    Description     Ids
    Salzburg        '6192,6193,6194'
    Innsbruck       '6196,6197,6198'

I saw some similar code on this site [How to concatenate text from multiple rows into a single text string in SQL server?, but I couldn't figure it out yet for my purpose (don't want to use XML Path!). Here is what I have tried so far:

DECLARE @ids AS Nvarchar(MAX)
SELECT  @ids = COALESCE(@ids + ',', '') + CAST(t.Id AS nvarchar(5))
    FROM (SELECT tmp.Id FROM (SELECT id, [Description] FROM tblMasterPropValues WHERE IdCategory = 253 AND IsActive = 1) as tmp
        WHERE [Description] = tmp.[Description]) AS t
SELECT @ids
--SELECT DISTINCT [Description], @ids AS IDs FROM tblMasterPropValues WHERE IdCategory = 253 AND IsActive = 1 AND Id IN (@ids)

I can't really get my head around it, and would appreciate any help on it.

Answer 1

You can try using STUFF() function

SELECT description,  Ids = STUFF(
             (SELECT ',' + Id
              FROM tblMasterPropValues t1
              WHERE t1.description = t2.description
              FOR XML PATH (''))
             , 1, 1, '') from tblMasterPropValues t2
group by description;