How to use GROUP BY to concatenate different columns in SQL Server?

Question

How do i get:

ID  Description     Details
1   A               Details 1
1   B               Details 2
2   A               Details 3
2   A               Details 4
2   B               Details 5
3   B               Details 6
3   B               Details 7
3   B               Details 8

TO:

ID  Values
1   A: Details 1 - B: Details 2
2   A: Details 3, Details 4 - B:Details 5
3   B: Details 6, Details 7, Details 8

I tried to follow the solution in this thread: How to use GROUP BY to concatenate strings in SQL Server?

but this gives:

ID  Values
1   A: Details 1 - B: Details 2
2   A: Details 3, A: Details 4 - B:Details 5
3   B: Details 6, B:Details 7, B:Details 8

Is there a way to not repeat the description column in the result set?

Thanks!

Can you post the query you've used to get the result? Most likely you'll need to remove the `Description` column from the inside concatenation of `STUFF` to outside, so you'll only get `B` or `A` once. — Radu Gheorghiu, May 16 '16 at 07:10
Do the entire path on : `Tbl.description + ': ' + tbl.Details` , that will provide the desired results. — sagi, May 16 '16 at 07:16

score 3 · Accepted Answer · edited Jan 18 '21 at 12:31

3

Use ROW_NUMBER to only add the Description on the first item:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY Id, Description ORDER BY Details)
    FROM #tbl
)
SELECT
    ID,
    [Values] =
        STUFF((
            SELECT 
                CASE WHEN rn = 1 THEN ' - ' + Description + ': ' ELSE ', ' END  + Details
            FROM Cte
            WHERE Id = t.Id
            ORDER BY Description, Details
            FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
        , 1, 2, '') 
FROM #tbl t
GROUP BY Id

ONLINE DEMO

edited Jan 18 '21 at 12:31

Community

1
1

answered May 16 '16 at 07:17

Felix Pamittan

31,544
7
41
67

1

I think there should be a difference when concating desc A and desc B (`, / -`) – sagi May 16 '16 at 07:23
1

@sagi, Thanks for catching that. I'm busy at the moment. I'll update later. – Felix Pamittan May 16 '16 at 07:25
1

I think your solution is good enough, but to do it perfectly you should use `LEAD/LAG` to see if the last rank that was concated was bigger or equal to this one, if so, use `-` , else `,` . – sagi May 16 '16 at 07:48
@sagi, Thanks. I think the `LEAD/LAG` solution can be posted as a new answer. I'll leave it to you. =) – Felix Pamittan May 16 '16 at 07:53

score 2 · Answer 2 · edited May 16 '16 at 08:01

I have modified the query to get the required output.Kindly replace the #tew with your table name. I hope it is helpful for you.

;with cte as 
(SELECT distinct ID,Description + ':' +
         STUFF(
               (SELECT      ',' + SubTableUser.Details
               FROM      #tew AS SubTableUser
               WHERE      SubTableUser.ID = outerTable.ID and SubTableUser.Description = outerTable.Description
               FOR XML PATH('')), 1, 1, '') AS Details

FROM   #tew as outerTable
 )
select distinct ID,
         STUFF(
               (SELECT      ',' + SubTableUser.Details
               FROM      cte AS SubTableUser
               WHERE      SubTableUser.ID = outerTable.ID 
               FOR XML PATH('')), 1, 1, '') AS Details

FROM  cte as outerTable
order by ID

Without using Value with XML-PATH might lead to parsing errors for XML encoded values. https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/ — vCillusion, Jun 02 '18 at 22:27

How to use GROUP BY to concatenate different columns in SQL Server?

2 Answers2