MySQL Table pivot - Dynamic

Question

Sooo...

Currently recreating a custom build website in Wordpress and I need to transfer the content.

I created a temp table containing the data I need with following structure naam_theater (varchar) which is the id naam (varchar) which is key waarde (varchar) which is value

I need to have to get a these in a csv with on row for each naam_theater with columns for each naam displaying waarde.

So I came up with this query:

SET @@group_concat_max_len = 320000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'IF(waarde = ''',
      naam,
      ''', waarde, NULL) AS ',
      naam
    )
  ) INTO @sql
FROM
  temp_theaters;
SET @sql = CONCAT('SELECT naam_theater, ', @sql, ' 
                  FROM temp_theaters 
                  GROUP BY naam_theater');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

When I run this, I get an error #1064 saying I have an error in my SQL syntax near 'plaats, IF(waarde = 'Postadres postcode', waarde, NULL) AS Postadres postcode, IF('

So I gues the problem is caused by spacing in my fields (becoming a column right now). But I don't seem to be able to find a solution on this.

Any advice?

UPDATE

Currently using this query, the 0 rows result was due to issues in phpmyadmin:

SET @@group_concat_max_len = 32000000;
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(waarde = ''',
      naam,
      ''', waarde, NULL)) AS `',
      naam,
      '`'
    )
  ) INTO @sql
FROM
  temp_theaters;
SET @sql = CONCAT('SELECT naam_theater, ', @sql, ' 
                  FROM temp_theaters 
                  GROUP BY naam_theater');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The result I'm getting right now is getting closer. Only, my fields (except for naam_theater) are null right now. While they do have content in the table.

I'm not sure on how I could best include table data so I'm just insterting this screenshot of it.

Kinda hard to debug your syntax error if the indicated text is elided. — Rick James, Nov 24 '16 at 00:54

Barmar · Accepted Answer · 2016-11-24T02:34:40.280

1

The problem is that your names have spaces in them, so you need to put them in backticks when you use them as column aliases.

You're also missing the call to a grouping function like SUM() or MAX() to get all the rows for a group into the same row in the result.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(naam = ''',
      naam,
      ''', waarde, NULL)) AS `',
      naam,
      '`'
    )
  ) INTO @sql
FROM
  temp_theaters;

edited Nov 24 '16 at 02:34

answered Nov 23 '16 at 20:41

Barmar

741,623
53
500
612

Hi @Barmar thanks for the response! I've made these changes and was able to run the query but... The output it get is only this: SELECT naam_theater, IF(waarde = 'Postadres plaats... – Maarten Nov 23 '16 at 20:49
That's what you would get if you did `SELECT @sql`. You should still have the `PREPARE` and `EXECUTE` statements. – Barmar Nov 23 '16 at 20:53
Yes, my bad. I've changed it to do prepare and execute as in opening post but I'm getting an empty result. – Maarten Nov 23 '16 at 20:59
You need a call to `MAX` in the generated code, see the updated answer. – Barmar Nov 23 '16 at 21:10
Can you post some sample table contents and the result you're trying to get? – Barmar Nov 23 '16 at 21:22
Added some extra information in the opening post, including example data. – Maarten Nov 24 '16 at 02:10
you should be testing `naam` in the `IF()`, not `waarde`. – Barmar Nov 24 '16 at 02:34

MySQL Table pivot - Dynamic

UPDATE

1 Answers1

Linked