Maarten Maarten - 8 days ago 5
MySQL Question

MySQL Table pivot - Dynamic

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.
I'm not sure on how I could best include table data so I'm just insterting this screenshot of it.

Answer

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;
Comments