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.

score 1 · Accepted Answer · answered Nov 20 '18 at 11:17

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;

answered Nov 20 '18 at 11:17

Fahmi

37,315
5
22
31

I don't want to use XML Path! – Developer Nov 20 '18 at 11:21
2

I'm going with your solution, even so it uses XML Path. Thanks a lot. – Developer Nov 20 '18 at 11:27
1

That's good to hear. I was preparing a solution with a cursor, but cursors are to be avoided. – George Menoutis Nov 20 '18 at 11:28
Here from 2020, using this solution! TKS! – Lari Sep 03 '20 at 21:19

score 1 · Answer 2 · answered Nov 20 '18 at 11:21

For that FOR XML PATH() is the right clause so, you can do :

SELECT DISTINCT v.description, STUFF(v1.ids, 1, 1, '''') + '''' 
FROM tblMasterPropValues v CROSS APPLY
     (SELECT ', '+ CAST(v1.Id AS VARCHAR(255))
      FROM tblMasterPropValues v1
      WHERE v1.description = v.description 
      FOR XML PATH('')
     ) v1(ids);

score 1 · Answer 3 · answered Nov 20 '18 at 12:02

You can also make it by using recursive CTE

DECLARE @tblMasterPropValues TABLE (Id INT, Description VARCHAR(20))
INSERT INTO @tblMasterPropValues VALUES
(6192 , 'Salzburg'),
(6193 , 'Salzburg'),
(6194 , 'Salzburg'),
(6196 , 'Innsbruck'),
(6197 , 'Innsbruck'),
(6198 , 'Innsbruck'),
(6199 , 'Innsbruck'),
(6201 , 'Bregenz'),
(6202 , 'Bregenz'),
(6203 , 'Bregenz')



;WITH Tbl AS 
(
    SELECT 
        *, 
        ROW_NUMBER() OVER(PARTITION BY Description ORDER BY Id) AS RN,
        COUNT(*) OVER(PARTITION BY Description) AS CNT
    FROM @tblMasterPropValues 
)
, Rcr AS (
    SELECT *, CAST(Id AS varchar(max)) Ids 
    FROM Tbl WHERE RN = 1
        UNION ALL
    SELECT T.*, Rcr.Ids + ',' + CAST(T.Id AS VARCHAR(10)) Ids 
    FROM Rcr 
        INNER JOIN Tbl T ON T.RN = Rcr.RN + 1 and Rcr.Description = T.Description
)
SELECT RN, Description, Ids FROM Rcr 
WHERE RN = CNT

Result:

Description          Ids
-------------------- -----------------------
Salzburg             6192,6193,6194
Innsbruck            6196,6197,6198,6199
Bregenz              6201,6202,6203

score 0 · Answer 4 · answered Nov 20 '18 at 11:24

Try this:

DECLARE @Table TABLE(ID INT, Description VARCHAR(25))
INSERT INTO @Table
VALUES (6192,'Salzburg' )
,(6193,'Salzburg' )
,(6194,'Salzburg' )
,(6196,'Innsbruck')
,(6197,'Innsbruck')
,(6198,'Innsbruck')
,(6199,'Innsbruck')
,(6201,'Bregenz'     )
,(6202,'Bregenz'     )
,(6203,'Bregenz'     )

Query:

SELECT DISTINCT T2.Description, 
    SUBSTRING(
        (
            SELECT ','+CAST(T1.ID AS VARCHAR)  AS [text()]
            FROM @Table T1
            WHERE T1.Description = T2.Description
            ORDER BY T1.Description
            FOR XML PATH ('')
        ), 2, 1000) [Ids]
FROM @Table T2

Result:

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

Combine Multi Rows with COALESCE

4 Answers4