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!

The question/answer you linked to appears to do exactly what you are asking, for the RDBMS it relates to. — Stu
What is your DBMS? You want to aggregate your data and convert single strings to a comma-separated list. How to do this differs from one DBMS to another. Always tag your SQL requests with the DBMS you are using. — Thorsten Kettner
@ThorstenKettner thanks for that tip, I'll update the question. DBMS is Microsoft SQL Server 2012. — Ryan Barnes
@Stu correct, but it also only returns the initial column and the 'student names'. I have 5 or 6 other columns that I need to also include but don't need to group. I don't know enough SQL magic to also include those fields given the example in the linked question. — Ryan Barnes

Debarko Debarko · Accepted Answer · 2022-07-28T16:48:01

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

SQL Group Multiple Results from Single Table

1 Answers