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

possible duplicate of MySQL pivot row into dynamic number of columns — RichardTheKiwi
– RichardTheKiwi, Commented May 3, 2013 at 9:08

Taryn · Accepted Answer · 2012-08-10 10:40:07Z

13

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

edited Aug 10, 2012 at 10:40

answered Aug 10, 2012 at 10:33

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

1 Comment

georgepsarakis Over a year ago

+1 for implementing prepared statements. Dynamic SQL is probably underappreciated. The caveat: cannot be used in functions or triggers.

Collectives™ on Stack Overflow

Select dynamic Columns in mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related