[SQL] Turn cell values into columns in MySQL

I have a database that looks like this:

| EMAIL | LIST | STATUS | ISP_GROUP | SENT_DATE | CREATED_DATE |
----------------------------------------------------------------------------------
| email1 | list1 | active | GMAIL | 2024-06-01 | 2024-05-30 |
----------------------------------------------------------------------------------
| email1 | list2 | complain | GMAIL | 2024-06-12 | 2024-05-10 |
----------------------------------------------------------------------------------
| email2 | list1 | unsubscribe | YAHOO | 2024-05-25 | 2024-05-01 |
----------------------------------------------------------------------------------
| email2 | list2 | active | YAHOO | 2024-06-15 | 2024-05-15 |
----------------------------------------------------------------------------------
| email2 | list3 | active | YAHOO | 2024-06-20 | 2024-06-11 |
----------------------------------------------------------------------------------


An email can be on multiple LISTs and have a different STATUS for each LIST.

An email will only ever have be on ISP_GROUP. In the example above, email1 is a GMAIL account. email1 could be on 12 LISTs but will always be a GMAIL account.

I need to make each LIST into it's own column, then list the STATUS for each list in a subsequent column, and also have the SENT_DATE and CREATED_DATE in their own columns.

Here is an example of what I'm trying to achieve:

| EMAIL | ISP_GROUP | LIST1_STATUS | LIST1_SENT_DATE | LIST1_CREATED_DATE | LIST2_STATUS | LIST2_SENT_DATE | LIST2_CREATED_DATE | and so on.. |
------------------------------------------------------------------------------------------------------------------------------------------------
| email1 | GMAIL | active | 2024-06-01 | 2024-05-30 | complain | 2024-06-12 | 2024-05-10 |
------------------------------------------------------------------------------------------------------------------------------------------------
| email2 | YAHOO | unsubscribe | 2024-05-25 | 2024-05-01 | active | 2024-06-15 | 2024-05-15 |
------------------------------------------------------------------------------------------------------------------------------------------------


So something like that.

There are 15 list total, then the results would be a really long dataset.

If the email is not on a particular LIST, then it can be NULL.

I have written a query like this:

SELECT
`email`
,`isp_group`
, GROUP_CONCAT(`Lists`) AS 'Lists'
FROM `list_main`
GROUP BY `email`, `isp_group`
ORDER BY `email` DESC


That returns a result like this:

| EMAIL | ISP_GROUP | LISTS |
----------------------------------------------------
| email1 | GMAIL | list1, list2 |
----------------------------------------------------
| email2 | YAHOO | list1, list2, list3 |
----------------------------------------------------


But I want to make each LIST their own column as well as the DATES. I'm not sure how to do this.

Can this even be achieved? If so, please help.

Thank you.

Continue reading...