SQL Group Multiple Results from Single Table

Question

I have a table that contains a line entry for each item that I need to group into an output with all the values of multiple rows that have the same uniqueID into one column result. The ItemType will be different but the UniqueID and OtherData shouldn't change as it's tied directly to the UniqueID, but I'm not 100% sure as there are well over 2M lines in this DB. I have seen similar questions and they would appear to do what I would like them to do, but the answers are over simplified and usually only include the unique id and the field they want on one line. I need to include about 5 other columns but I don't need to any anything fancy to them. Just trying to group results from the one column as well as return the other columns (that are likely not never be different).

To over simplify the data set this is what it looks like followed by what I'd like to do.

Example Table:

UniqueID | ItemType   | OtherData
----------------------------------
1234     | apples     | 123.1.123.1
1234     | oranges    | 123.1.123.1
2233     | red fish   | 123.5.67.2
1234     | grapes     | 123.1.123.1
2233     | blue fish  | 123.5.67.2

Desired Result:

UniqueID | ItemType   | OtherData
----------------------------------
1234     | apples, oranges, grapes | 123.1.123.1
2233     | red fish, blue fish     | 123.5.67.2

I've tried a couple versions of SELECT DISTINCT and GROUP BY but that either returns the same as if I didn't or some other undesirable result. Also tried STRING_AGG but that only works on MSSQL2017. Any help you can provide would be much appreciated, thank you!

Answer 1

Building on the answer from the previous link you can create a cte then execute the query

This will given you the

SELECT Main.UniqueID,
       LEFT(Main.ItemTypes,Len(Main.ItemTypes)-1) As "ItemTypes"
FROM
    (
        SELECT DISTINCT ST2.UniqueID, 
            (
                SELECT ST1.ItemType + ',' AS [text()]
                FROM dbo.TheTable ST1
                WHERE ST1.UniqueID = ST2.UniqueID
                ORDER BY ST1.UniqueID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') ItemTypes
        FROM dbo.TheTable ST2
    ) [Main]

Once you have that you can build this into a cte with the with statement then join back on the table to get the rest of the data.

with ItemTypes as
(
SELECT Main.UniqueID,
       LEFT(Main.ItemTypes,Len(Main.ItemTypes)-1) As "ItemTypes"
FROM
    (
        SELECT DISTINCT ST2.UniqueID, 
            (
                SELECT ST1.ItemType + ',' AS [text()]
                FROM dbo.TheTable ST1
                WHERE ST1.UniqueID = ST2.UniqueID
                ORDER BY ST1.UniqueID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') ItemTypes
        FROM dbo.TheTable ST2
    ) [Main]
) 

Select Distinct TheTable.UniqueID, ItemTypes.ItemTypes, TheTable.OtherData
from TheTable join ItemTypes 
    on (TheTable.UniqueID = ItemTypes.UniqueID)

Results

UniqueID  ItemTypes                  OtherData
--------- -------------------------- --------------------------------
1234      apples,oranges,grapes      OtherData
2233      red fish,blue fish         OtherData

There are a few expensive operations this will be an expensive query to run. but with 2million rows should be ok with a good server.