MySQL dynamic pivot table

Question

Im trying to get a pivot table with dynamic columns to work. When user_id is a string, it works fine but if its an int, then it seems to fail

Here is what I have so far with the assistance of past questions:

CREATE TABLE measure2
    (`inspection_date` date, `user_id` int, `score` int, comment text)
;

INSERT INTO measure2
    (`inspection_date`, `user_id`, `score`, comment)
VALUES
    ('2012-10-16', 0, 0, null),
    ('2012-10-16', 1, 0, null),
    ('2012-10-16', 2, 0, null),
    ('2012-10-16', 3, 0, null),
    ('2012-10-17', 0, 1, null),
    ('2012-10-17', 1, 1, null),
    ('2012-10-17', 2, 1, null),
    ('2012-10-18', 3, 1, null)
;


SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ''',
      user_id,
      ''' then score end) AS ',
      user_id
    )
  ) INTO @sql
FROM  measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, '
                  FROM measure2
                  GROUP BY inspection_date');

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

see: http://sqlfiddle.com/#!2/eab24/1

Im sure its something simple, but what am I missing?

Thanks

Taryn · Accepted Answer · 2013-07-21 14:12:51Z

2

Since the values are in int you are are making them the column names, you have to wrap the values in a backtick

The sql will look like:

max(case when user_id = 1 then score end) as `1`

The full query will be:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ''',
      user_id,
      ''' then score end) AS `',
      user_id, '`'
    )
  ) INTO @sql
FROM  measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, ' 
                  FROM measure2 
                  GROUP BY inspection_date');

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

See SQL Fiddle with Demo

answered Jul 21, 2013 at 14:12

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3660378 Over a year ago

hello sir, As I Am digging some dynamic pivot table, this caught my attention.. and I want to know How this query works in php or how I could do this query in php

user359040 · Accepted Answer · 2013-07-21 14:12:55Z

1

Simple indeed - numbers alone aren't valid column names in SQL, so you need to amend your code to enclose them in backticks:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when user_id = ',
      user_id,
      ' then score end) AS `',
      user_id,
      '`'
    )
  ) INTO @sql
FROM  measure2;

SET @sql = CONCAT('SELECT inspection_date, ', @sql, ' 
                  FROM measure2 
                  GROUP BY inspection_date');

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

I suggest retaining this change for string values as well, in case some of your string values match reserved words or include spaces.

(Also, ' aren't required around numeric values, so I have removed them from the case clauses.)

SQLFiddle here.

answered Jul 21, 2013 at 14:12

user359040

1 Comment

Mitchell Weiss Over a year ago

Thanks as well! You and bluefeet must have posted at almost the exact same time!

Collectives™ on Stack Overflow

MySQL dynamic pivot table

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related