CODEWITHSUNDEEP
sqlmariadb
ChiefKeith
ChiefKeith

Reputation: 83

SQL function to return multiple rows with matching ID's into a single row

I'm currently working on a front-end tool that is requiring me to write a query that will return multiple rows of data into a singular row. It may be important to note that this SQL query is dynamically generated based upon user selections. I'm not the greatest at explaining what it is exactly that needs to be done, so let me show you.

Table 1:

PersonnelID | Product | Intermediate | Advanced
1234          Product1    Module1      Module2
1234          Product2                 Module3
1234          Product3    Module4
123456        Product1    Module1      Module2 
123456        Product2                 Module2

User's can specify as many products or modules as they want and the query needs to return those with the particular product(s) and module(s). My original approach was to do this all via SQL, however I am now more open to doing some client-side manipulation when the data is returned if this is simply too difficult/too much load for my server.

I have been trying to write a query to return ALL of the product entries for each particular personnelID, after failing multiple times at the above goal. For example, 1234 would return a single row with Product 1, 2, and 3, in addition Module 1 and Module 4 under intermediate, and Module 2 and 3 under advanced, like this:

PersonnelID | Product                       | Intermediate          | Advanced
1234          Product1, Product2, Product3    Module 1, Module 4     Module 2, Module 3
123456        Product1, Product2              Module 1               Module 2, Module 3

My experience with SQL is rather basic, so my apologies if this question has been asked previously or is a rather mundane approach, I'm not very skilled with phrasing my questions or searching with SQL quite yet.

I have already tried using GROUP_CONCAT(), however could not get it working as intended. It would return every instance of every product as it appeared throughout the table and ended up taking around ~55 seconds to execute, which is simply unacceptable.

Overall, I need to display who has specific product(s) and/or module(s) that have been selected by a user. If my approach seems backwards/wrong, please let me know, I don't just want an answer I would like to learn :)

Upvotes: 0

Views: 662

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The answer to the question you are asking appears to be:

select PersonnelID,
       group_concat(product),
       group_concat(intermediate),
       group_concat(advanced),
from t
group by PersonnelID;

You should ask another question about performance, if that is an issue. Be sure to include more information on table size, indexes, and the query you are using.

Upvotes: 1

Related Questions