Creating a dynamic pivot in Mysql

Question

I have table which holds contact information on clients. The table holds email addresses and other personal data, plus 300 a 400 columns with list names where the contact is either subscribed, unsubscribed or null (so not in that list at all). For each list (column name) I have an additional column holding a date ( of subscribing or unsubscribing)

It is very hard to handle this list.

Now I am trying to make a query which gives me all the mail addresses, with the names of the column where the field is not null. In that way I can see which client is in which list. I wanted to do it dynamically ( I have different lists with different number of columns so I can reuse the code)

I have been trying it with Case ( not so dynamic), but is takes a long way of coding.

I know that there ar other similar questions, but I don't quite understand the answers. This is also my first question here, suggestions to improve are always welcome.

Many thanks in advance.Example of the code should be like:

SELECT  test.email,
    (
        CASE 
            WHEN  Test.Column_Status <>'0'
            THEN Columname 
            ELSE 0 
        END
    )
FROM  test
GROUP BY test.email

Note that in the absence of any aggregating functions, a GROUP BY clause is never appropriate. For further help, see: Why should I provide an MCRE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Jan 13, 2020 at 15:05
Thanks for that one, i am rather new at Mysql,, goals was to make sure each mail ontly has one entry in the table. — Bjorn Huwaert
– Bjorn Huwaert, Commented Jan 13, 2020 at 15:07
Your core problem is a denormalised structure. You should have a separate table for subscriptions. — Strawberry
– Strawberry, Commented Jan 13, 2020 at 15:26

O. Jones · Accepted Answer · 2020-01-13 16:14:53Z

With respect, your problem is almost impossible to solve with pure MySQL queries. You have discovered already how big your queries will be even for a static pivot. To do dynamic pivoting (where the column names are determined by the actual data in your table) requires a MySQL feature confusingly called Prepared Statements. These are a gigantic pain in the neck to develop, debug, and maintain.

You will be much better off doing this work with a programming language (php, nodejs, c#, java, even PERL) with good list-manipulation features.

@Strawberry is also correct that you should normalize this data. Instead of a table with

data
  id
  email
  status1
  status2
  status4
   ...
  status400

Use two tables:

data
  id 
  email

detail
  id
  itemname 
  value

So, a row like this in your table

 id  email               is_mouse   is_duck   is_dog   is_cartoon_character
 17  [email protected]     1           0         0         1

will look like this

data
   17 [email protected]

detail
   17   'is_mouse'               1
   17   'is_cartoon_character'   1
   17   'is_duck'                0   (you can omit this row ... )
   17   'is_dog'                 0   (... and this one, if you wish)

Most database designers prefer this normalized form. One good reason: it's easier to add new attributes like 'is_ninja_turtle' than it would be if each attribute were a column.

Collectives™ on Stack Overflow

Creating a dynamic pivot in Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related