What's the best way to combine text using GROUP BY in SQL Server?

Data and Databases
1

Hey everyone, I’m trying to figure out how to merge some data in SQL Server. I’ve got a table that looks like this:

CustomerId    ItemName    ItemValue
1             Apple       4
1             Banana      8
2             Cherry      9

I want to turn it into something like this:

CustomerId    Items
1             Apple:4, Banana:8
2             Cherry:9

Is there a way to do this with GROUP BY? I’ve tried a few things but can’t seem to get it right. Any help would be awesome! Thanks in advance!

2

For SQL Server versions prior to 2017, you can utilize the STUFF function in combination with FOR XML PATH. This approach is particularly effective for concatenating strings within a GROUP BY clause. Here’s an example:

SELECT CustomerId,
    STUFF((
        SELECT ', ' + ItemName + ':' + CAST(ItemValue AS VARCHAR(10))
        FROM YourTable t2
        WHERE t2.CustomerId = t1.CustomerId
        FOR XML PATH('')
    ), 1, 2, '') AS Items
FROM YourTable t1
GROUP BY CustomerId

This method constructs a comma-separated list of items for each customer, efficiently combining the ItemName and ItemValue. The STUFF function is used to remove the leading comma and space from the result.

3

hey luke, try using STRING_AGG in SQL Server 2017+. it’s pretty neat:

SELECT CustomerId, STRING_AGG(CONCAT(ItemName, ':', ItemValue), ', ') AS Items
FROM YourTable
GROUP BY CustomerId

does that work for ya?

4

hey, tried xml path? it’s neat for older sql versions. use stff to remove the comma. ex:

SELECT CustomerId,
  STUFF((SELECT ', ' + Itemname + ':'+ CAST(ItemValue AS VARCHAR(10))
         FROM yourtable
         WHERE CustomerId = t1.CustomerId
         FOR XML PATH('')), 1, 2, '') as Items
FROM yourtable t1
GROUP BY CustomerId

does that work for u?