CODEWITHSUNDEEP
mysqlsqlcrosstab
Louis Papaloizou
Louis Papaloizou

Reputation: 282

MySQL pivot / crosstab

I have the following table

date_time,channel_code,value
"2013-09-30 08:50:00",no,0.01
"2013-09-30 08:50:00",no2,0.57
"2013-09-30 08:50:00",nox,0.58
"2013-09-30 09:00:00",no,0.03
"2013-09-30 09:00:00",no2,0.59
"2013-09-30 09:00:00",nox,0.62
"2013-09-30 09:10:00",no,0.03
"2013-09-30 09:10:00",no2,0.63
"2013-09-30 09:10:00",nox,0.66
"2013-09-30 09:20:00",no,0.02
"2013-09-30 09:20:00",no2,0.65
"2013-09-30 09:20:00",nox,0.68
"2013-09-30 09:30:00",no,0.04
"2013-09-30 09:30:00",no2,0.74
"2013-09-30 09:30:00",nox,0.78

I am trying to get the data in the following form: 

date_time,no,no2,nox
"2013-09-30 08:50:00",0.01,057,0.58

with no luck so far. The database is MySQL, I've read here that it does not have a PIVOT function. Also I had a look on similar posts (like this) but I guess my SQL skill only takes me this far :-)

Any help appreciated

Upvotes: 1

Views: 155

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can write your dynamic pivot query as below

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN channel_code = ''',
         channel_code, 
         ''' THEN value  END) `',
         channel_code,'`'
         )
          ORDER BY date_time,channel_code ASC        
 )
  INTO @sql
  FROM test;

SET @sql = CONCAT('SELECT date_time, ', @sql, ' 
                     FROM test
                    GROUP BY date_time');

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

Demo

Or its simple if you have limited/determined channel_code then 

SELECT
date_time,
MAX(CASE WHEN channel_code = 'no' THEN value  END) `no`,
MAX(CASE WHEN channel_code = 'no2' THEN value  END) `no2`,
MAX(CASE WHEN channel_code = 'nox' THEN value  END) `nox`
FROM test
GROUP BY date_time

Demo

Upvotes: 2

Related Questions