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
– Radu Gheorghiu, Commented May 16, 2016 at 7:10
Do the entire path on : Tbl.description + ': ' + tbl.Details , that will provide the desired results. — sagi
– sagi, Commented May 16, 2016 at 7:16

Community · Accepted Answer · 2021-01-18 12:31:40Z

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, 2021 at 12:31

CommunityBot

11 silver badge

answered May 16, 2016 at 7:17

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sagi Over a year ago

I think there should be a difference when concating desc A and desc B (, / -)

Felix Pamittan Over a year ago

@sagi, Thanks for catching that. I'm busy at the moment. I'll update later.

sagi Over a year ago

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

Felix Pamittan Over a year ago

@sagi, Thanks. I think the LEAD/LAG solution can be posted as a new answer. I'll leave it to you. =)

TT. · Accepted Answer · 2016-05-16 08:01:32Z

2

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

edited May 16, 2016 at 8:01

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered May 16, 2016 at 7:56

Chirag

312 bronze badges

1 Comment

vCillusion Over a year ago

Without using Value with XML-PATH might lead to parsing errors for XML encoded values. blog.vcillusion.co.in/…

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related