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.

Answer 2

What you need is a group concat query in MySQL.

Your query will be

select UniqueID, GROUP_CONCAT(ItemType) ItemTypes, GROUP_CONCAT( DISTINCT OtherData) Otherdata from ExampleTable GROUP BY UniqueID;

A sample reference you can check here:

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

Answer 3

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.