I have a table 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. How can I do this?
A pivot query with a variable number of columns always requires two passes. You need to know how many columns, because an SQL query must have a fixed set of columns in its select-list before it begins reading data. You can't make an SQL query that expands itself based on the data it reads.
Here's an example of a query that produces the pivot table you describe:
SELECT
email,
MAX(isp_group),
-- columns for list1
MAX(CASE list WHEN 'list1' THEN status END) AS LIST1_STATUS,
MAX(CASE list WHEN 'list1' THEN sent_date END) AS LIST1_SENT_DATE,
MAX(CASE list WHEN 'list1' THEN created_date END) AS LIST1_CREATED_DATE,
-- columns for list2
MAX(CASE list WHEN 'list2' THEN status END) AS LIST2_STATUS,
MAX(CASE list WHEN 'list2' THEN sent_date END) AS LIST2_SENT_DATE,
MAX(CASE list WHEN 'list2' THEN created_date END) AS LIST2_CREATED_DATE
-- repeat the set of three columns for each list, up to the max number of lists
FROM list_main
GROUP BY email;
Output given your sample data:
+--------+----------------+--------------+-----------------+--------------------+--------------+-----------------+--------------------+
| email | MAX(isp_group) | LIST1_STATUS | LIST1_SENT_DATE | LIST1_CREATED_DATE | LIST2_STATUS | LIST2_SENT_DATE | LIST2_CREATED_DATE |
+--------+----------------+--------------+-----------------+--------------------+--------------+-----------------+--------------------+
| 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 |
+--------+----------------+--------------+-----------------+--------------------+--------------+-----------------+--------------------+
Dbfiddle, tested with MySQL 5.7: https://www.db-fiddle.com/f/gjxHuzM7hKeyidVu8Q7GGj/0
Writing such a query requires you run an initial query to discover what lists exist:
SELECT DISTINCT list FROM list_main;
Given the result of that query, write application code to loop through the lists and format the columns of the pivot query.
The alternative, as the comments above suggested, is not to do the pivot in SQL, but just fetch all the data as it is stored in the database, and write application code to arrange it into the tabular presentation you want.
One way or the other, you need a client application where you can write the code to do the pivot.