MySQL combines multiple rows into one and supports column names

Question

Tables

Tables and some dummy data to illustrate the problem.

Members

Saves basic member information.

------------------------------------ | member_id | email | ------------------------------------ | 1 | [e-mail] | ------------------------------------ | 2 | [e-mail] | ------------------------------------ | 3 | [e-mail] | ------------------------------------ | 4 | [e-mail] | ------------------------------------ | 5 | [e-mail] | ------------------------------------ 

member_data h2>

Saves some additional metadata for each member

 ---------------------------------------- | member_id | name | surname | company | ---------------------------------------- | 1 | A | A | A | ---------------------------------------- | 2 | B | B | B | ---------------------------------------- | 3 | C | C | C | ---------------------------------------- | 4 | D | D | D | ---------------------------------------- | 5 | E | E | E | ---------------------------------------- 

Category

Different categories included in the system.

 ------------------------------------ | cat_id | cat_name | ------------------------------------ | 1 | Cars | ------------------------------------ | 2 | Bikes | ------------------------------------ | 3 | Boats | ------------------------------------ 

licenses

The participant must have a license in order to have access to the category.

 ----------------------------------------------------------------------- | id | subid | catid | start_date | end_date | description | ----------------------------------------------------------------------- | 1 | 1 | 1 | 2014-01-01 | 2020-12-31 | Premium | ----------------------------------------------------------------------- | 2 | 1 | 2 | 2014-01-01 | 2015-12-31 | Premium | ----------------------------------------------------------------------- | 3 | 1 | 3 | 2014-01-01 | 2018-12-31 | Premium | ----------------------------------------------------------------------- | 4 | 2 | 1 | 2014-01-01 | 2016-12-31 | Premium | ----------------------------------------------------------------------- | 7 | 3 | 1 | 2014-01-01 | 2014-01-02 | Premium | ----------------------------------------------------------------------- | 8 | 3 | 2 | 2014-01-01 | 2014-01-02 | Premium | ----------------------------------------------------------------------- | 9 | 3 | 3 | 2014-01-01 | 2020-01-31 | Premium | ----------------------------------------------------------------------- | 10 | 5 | 1 | 2014-01-01 | 2014-01-02 | Premium | ----------------------------------------------------------------------- | 11 | 5 | 2 | 2014-01-01 | 2014-01-02 | Premium | ----------------------------------------------------------------------- | 12 | 5 | 3 | 2014-01-01 | 2014-01-02 | Premium | ----------------------------------------------------------------------- 

About data

Member 1 is licensed for categories 1,2 and 3. All of them are valid and valid. Member 2 is licensed only for category 1. He is active. Member 3 has a license of 1,2 and 3. Only the category 3 license is valid. Member 4 does not have a license. Member 5 has licenses for categories 1,2 and 3, but they all expired.

What am I trying to do Acewive

I want to get entries for each member license with their corresponding member_data and category. The license must exist and be valid for the category in which the member must return the data for this license.

In addition, I want each license to be returned as a single line that contains all the data required in the following format:

Output format

I want to withdraw members who have valid licenses and return either the expiration date for the category or not, if they do not have a license for the category, but keep it one by one. I.e:.

 ---------------------------------------------------------------------------------- | Company | Name | LicenceType | Cars | Bikes | Boats | ---------------------------------------------------------------------------------- | A |AA | Premium |2020-12-31 | 2015-12-31 | 2018-12-21 | ---------------------------------------------------------------------------------- | B |BB | Premium |2016-12-31 | | | ---------------------------------------------------------------------------------- | C |CC | Premium | | | 2020-01-31 | ---------------------------------------------------------------------------------- 

What i tried

 SELECT md.company as Company, CONCAT(md.name,' ', md.surname) as Name, l.description as LicenceType, (CASE WHEN (c.cat_name='Cars') THEN l.end_date ELSE '' END)AS Cars, (CASE WHEN (c.cat_name='Bikes') THEN l.end_date ELSE '' END)AS Bikes, (CASE WHEN (c.cat_name='Boats') THEN l.end_date ELSE '' END)AS Boats FROM licences as l JOIN categories as c ON c.cat_id=l.catid JOIN member_data as md ON md.member_id=l.subid WHERE l.end_date>='2014-12-17' AND (l.description='Premium') ORDER BY Company ASC 

Currently displays

Here's how the data is displayed:

 ---------------------------------------------------------------------------------- | Company | Name | LicenceType | Cars | Bikes | Boats | ---------------------------------------------------------------------------------- | A |AA | Premium |2020-12-31 | | | ---------------------------------------------------------------------------------- | A |AA | Premium | | 2015-12-31 | | ---------------------------------------------------------------------------------- | A |AA | Premium | | | 2018-12-21 | ---------------------------------------------------------------------------------- | B |BB | Premium |2016-12-31 | | | ---------------------------------------------------------------------------------- | C |CC | Premium | | | 2020-01-31 | ---------------------------------------------------------------------------------- 

The problem is that you can see a company A record showing three lines. I would like each of the three lines to be returned as one line in accordance with the output format shown above.

I would appreciate any ideas on how to achieve this. Thanks.

Answer 1

Use aggregation:

 SELECT md.company as Company, CONCAT(md.name,' ', md.surname) as Name, l.description as LicenceType, MAX(CASE WHEN (c.cat_name='Cars') THEN l.end_date ELSE '' END)AS Cars, MAX(CASE WHEN (c.cat_name='Bikes') THEN l.end_date ELSE '' END)AS Bikes, MAX(CASE WHEN (c.cat_name='Boats') THEN l.end_date ELSE '' END)AS Boats FROM licences as l JOIN categories as c ON c.cat_id=l.catid JOIN member_data as md ON md.member_id=l.subid WHERE l.end_date>='2014-12-17' AND (l.description='Premium') GROUP BY Company, Name, l.description ORDER BY Company ASC;