Select dynamic Columns in mysql

Question

Is it possible to traverse a table like this:

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)

Into this:

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

The aim is to select all the CRITERIA_IDs and use them as a column. As rows i like to use all the TYPEs .

• All Criterias: SELECT distinct(CRITERIA_ID) FROM stackoverflow.Results;
• All Types SELECT distinct(TYPE) FROM stackoverflow.Results;

But how combine them into a view or smth. like this?

If you like to play with the data. This is a 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. So you will need to use an aggregate function with a 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 perform this dynamically, meaning you do not know ahead of time the columns to transpose, then you should review the following article:

Dynamic pivot tables (transform rows to columns)

Your code would 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