Select dynamic columns in mysql

Question

Is it possible to navigate the table as follows:

  mysql> select * from `stackoverflow`.`Results`;
 + -------------- + --------- + ------------- + -------- +
 |  ID |  TYPE |  CRITERIA_ID |  RESULT |
 + -------------- + --------- + ------------- + -------- +
 |  1 |  car |  env |  1 |
 |  2 |  car |  gas |  |
 |  3 |  car |  age |  |
 |  4 |  bike |  env |  1 |
 |  5 |  bike |  gas |  |
 |  6 |  bike |  age |  1 |
 |  7 |  bus |  env |  1 |
 |  8 |  bus |  gas |  1 |
 |  9 |  bus |  age |  1 |
 + -------------- + --------- + ------------- + -------- +
 9 rows in set (0.00 sec)

In it:

  + ------ + ----- + ----- + ----- +
 |  TYPE |  env |  gas |  age |
 + ------ + ----- + ----- + ----- +
 |  car |  1 |  |  |
 |  bike |  1 |  |  1 |
 |  bus |  1 |  1 |  1 |
 + ------ + ----- + ----- + ----- +

The goal is to select all CRITERIA_ID and use them as a column. As strings I like to use all TYPE .

• All criteria: SELECT distinct(CRITERIA_ID) FROM stackoverflow.Results;
• All types SELECT distinct(TYPE) FROM stackoverflow.Results;

But how to combine them with performance or smth. like this?

If you like to play with data. This is the script to generate the table:

 CREATE SCHEMA `stackoverflow`; CREATE TABLE `stackoverflow`.`Results` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `TYPE` varchar(50) NOT NULL, `CRITERIA_ID` varchar(5) NOT NULL, `RESULT` bit(1) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB; INSERT INTO `stackoverflow`.`Results` ( `ID`, `TYPE`, `CRITERIA_ID`, `RESULT` ) VALUES ( 1, "car", env, true ), ( 2, "car", gas, false ), ( 3, "car", age, false ), ( 4, "bike", env, true ), ( 5, "bike", gas, false ), ( 6, "bike", age, true ), ( 7, "bus", env, true ), ( 8, "bus", gas, true ), ( 9, "bus", age, true ); 

Answer 1

Unfortunately, MySQL does not have a PIVOT function, which is basically what you are trying to do. Therefore, you will need to use the aggregate function with the CASE statement:

 SELECT type, sum(case when criteria_id = 'env' then result end) env, sum(case when criteria_id = 'gas' then result end) gas, sum(case when criteria_id = 'age' then result end) age FROM results group by type 

See SQL Fiddle with Demo

Now, if you want to do this dynamically, that is, you do not know the columns for transposition in advance, then you should look at the following article:

Dynamic pivot tables (converting rows to columns)

Your code will look like this:

 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(IF(CRITERIA_ID = ''', CRITERIA_ID, ''', RESULT, NULL)) AS ', CRITERIA_ID ) ) INTO @sql FROM Results; SET @sql = CONCAT('SELECT type, ', @sql, ' FROM Results GROUP BY type'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

See SQL Fiddle with Demo