SQL Group Multiple Results from Single Table-CodePudding

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.

What group concat does is that it will group all column values against that UniqID col and mark them in a comma separated format. Link to read more in depth about group concat can be found here: Link

CodePudding user response：

Group by all columns except for the item type. Then use the string aggregation function on the item type. In SQL Server this is STRING_AGG, but as you are using an antique version of the DBMS, you will have to emulate it. I am showing the proper query with STRING_AGGhere. Use Google or stackoverflow to see how it is emulated in your old SQL Server version.

select
  uniqueid,
  string_agg(itemtype, ', ') as itemtypes
  otherdata
from mytable
group by uniqueid, otherdata
order by uniqueid, otherdata;

Two remarks:

UniqueID is a funny name for an ID that is not unique but occurs multiple times in the table. Misnomers like this can lead to errors and bad maintainability.
As to "OtherData shouldn't change as it's tied directly to the UniqueID": This indicates that your data model is flawed and your database table is not normalized. You should have two tables instead, one for the unique data, one for the details.

CodePudding user response：

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.