CODEWITHSUNDEEP
sqlsql-server
ohhzumm
ohhzumm

Reputation: 98

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

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!

Upvotes: 0

Views: 107

Answers (2)

Chirag
Chirag

Reputation: 31

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

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 3

Related Questions