MySQL pivot table query with dynamic columns truncating key value

Question

I am trying to dynamically pivot the key value pairs in the table below.

+-----------------+-----------------------------+-------+
| id | category   | name                        | value |
+-----------------+-----------------------------+-------+
|  1 | acme       | 2fa                         |   0   |      
|  2 | acme       | abc_processing_date         | today |        
|  3 | acme       | activate_new_approve_person |   1   |         
|  4 | acme       | activate_new_schdule        |   1   |
|  5 | acme       | additional_footer_for_person|  NULL |   
+-----------------+-----------------------------+-------+

Running my query below I am getting the error

'-new-schedule,IFNULL(IF(z_tmp_admin_system_settings.name = 'additional_footer_fo' at line 1

I this developed using method described by Taryn in MySQL pivot table query with dynamic columns.

SET SESSION group_concat_max_len = 100000;
SET @sql = '';

SELECT GROUP_CONCAT(DISTINCT
                    CONCAT(
                            'IFNULL(IF(z_tmp_admin_system_settings.name = ''',
                            name,
                            ''', value, NULL), NULL) AS ',
                            name
                        )
           )
INTO @sql
FROM z_tmp_admin_system_settings;
SET @sql = CONCAT('SELECT subdomain, ', @sql, ' FROM name GROUP BY subdomain');

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

score 1 · Answer 1 · answered Oct 20 '19 at 23:52

There are several problems with your code - by descreasing gravity:

you need to select from z_tmp_admin_system_settings, not from name
the column to group by is called category, not subdomain
since the principle of the query is to use aggregation, you need an aggregate functions for the generated columns, such as MAX(); old versions of MySQL tolerate not using an aggregate function on non-aggregated columns, but that's not something to get accustomed to
it is a good practice to surround the name of the columns with backticks, in case one of the name clashes with a reserved word (this is not the case in your sample data, but it is probably not comprehensive)
DISTINCT is probably not needed, unless you have duplicated names per category (in this case, feel free to add it back to the below code)
Side note: IFNULL(..., NULL) is a no-op

Code:

SET SESSION group_concat_max_len = 100000;
SET @sql = '';

SELECT GROUP_CONCAT(
    CONCAT('MAX(IF(z_tmp_admin_system_settings.name = ''', name, ''', value, NULL)) AS `', name, '`')
)
INTO @sql
FROM z_tmp_admin_system_settings;
SET @sql = CONCAT(
    'SELECT category, ', 
    @sql, 
    ' FROM z_tmp_admin_system_settings GROUP BY category'
);

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

Demo on DB Fiddle:

| category | 2fa | abc_processing_date | activate_new_approve_person | activate_new_schdule | additional_footer_for_person |
| -------- | --- | ------------------- | --------------------------- | -------------------- | ---------------------------- |
| acme     | 0   | today               | 1                           | 1                    |                              |

How would I modify the statement to create a new table from the results — Adam, Oct 21 '19 at 00:02
@Adam: just `SET @sql = CONCAT('CREATE TABLE AS SELECT category, ', @sql, ...` — GMB, Oct 21 '19 at 00:04
getting row size is too large. Trying to add `ROW_FORMAT=DYNAMIC` to end of the `CREATE TABLE AS SELECT` but it did not work. Any suggestions? — Adam, Oct 21 '19 at 00:16
@Adam: hard to tell without seeing actual data. You would need to ask a *new* question, as comments are basically not meant for this. Also if my answer was helpful and properly responded to your *original* question, please upvote/accept it. Thanks! — GMB, Oct 21 '19 at 19:41

score 0 · Answer 2 · answered Oct 20 '19 at 23:43

It looks like some of your names have hyphens in them; one of them ends with -new-schedule (although the sample data posted has activate_new_schedule -- is that accurate?). You need to enclose the names in backticks to escape these names when using them as aliases.

SELECT GROUP_CONCAT(DISTINCT
                    CONCAT(
                            'IFNULL(IF(z_tmp_admin_system_settings.name = ''',
                            name,
                            ''', value, NULL), NULL) AS `',
                            name,
                            '`'
                        )
           )
INTO @sql
FROM z_tmp_admin_system_settings;

MySQL pivot table query with dynamic columns truncating key value

2 Answers2

Linked