MySQL combine multiple rows into one and maintain column names

Question

The Tables

The tables and some dummy data to illustrate the issue.

members

Stores basic information about the members.

------------------------------------
| member_id  | email               |
------------------------------------
| 1          | [email protected]             |
------------------------------------
| 2          | [email protected]             |
------------------------------------
| 3          | [email protected]             |
------------------------------------
| 4          | [email protected]             |
------------------------------------
| 5          | [email protected]             |
------------------------------------

member_data

Stores some extra meta data 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     |
----------------------------------------

categories

Different categories that are within the system.

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

licences

A member must have a licence to be able to access a 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 the data

Member 1 has a licence to categories 1,2 and 3. They are all active and valid. Member 2 has a licence to category 1 only. It is active. Member 3 has a licence to 1,2 and 3. Only the licence for category 3 is valid. Member 4 has no licences. Member 5 has licences for categories 1,2 and 3 but they are all expired.

What I'm Trying To Acheive

I want to get the records for each members' licence, with their respective member_data and category. A licence must exist and be valid for a category for the member to have data returned for that licence.

Furthermore, I want each of the licences that are returned to come back as one row, which contains all of the data required in the following format:

Output format

I want to output the members who hold valid licences, and return either their expiry date for a category or no output if they don't have a licence for a category but do hold one for another. I.e.:

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

What I've 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 outputs

This is how the data currently appears:

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

The issue is, as you can see for Company A's record, showing as three distinct rows. I would like to have each of the three rows returned as just a single row, as per the output format shown above.

I'd appreciate any ideas on how to achieve this. Thank you.

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;