0

I would like to switch the columns of a MySQL database to rows.

For a current database with this structure :

id    date    category    subcategory    value
001    date1    cat1        sub1        X
002    date1    cat1        sub2        X
003    date1    cat1        sub3        X
004    date1    cat2        sub1        X
005    date1    cat3        sub1        X
006    date1    cat3        sub2        X

And I would like to have this as a result :

date    cat1_sub1    cat1_sub2    cat1_sub3    cat2_sub1    cat3_sub1    cat3_sub2
date1        X           X            X            X            X            X

The database is pretty heavy so having this done by a script would take forever, so is it possible to have it done with a MySQL query?

For example, my SQL request is : 

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(data.timestamp), '%d %m %Y %h:%i:%s') as timestamp, 
    data_sub_category.designation as sub, 
    data_key.designation as dkey, 
    data_row.value as value
FROM data, data_row, data_sub_category, data_key, data_category
WHERE data.id = data_row.id_data
  AND data_category.id = data_row.id_data_category
  AND data_sub_category.id = data_row.id_data_sub_category
  AND data_key.id = data_row.id_data_key
  AND data_row.id_data_sub_category IN (1,2,3,22,1,2,3,22) 
  AND data_row.id_data_key IN (3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101,3,4,5,6,9,10,11,12,15,16,17,18,92,93,94,95,96,97,98,99,100,101) 
  AND data.timestamp BETWEEN 1536271200 AND 1536357540
ORDER BY data.timestamp

Thanks for your tips

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
V. MARTIN
  • 61
  • 1
  • 2
  • 10
  • you may check the good options provided in this similar question https://stackoverflow.com/questions/1241178/mysql-rows-to-columns – markrosario Sep 21 '18 at 10:29
  • Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Nick Sep 21 '18 at 10:37
  • I already read this. I can't know names from row. I use foreign key to get row name. I didn't know how to do the same with my SQL request... – V. MARTIN Sep 21 '18 at 10:40
  • You mention application code (PHP), so handle this kind of thing there. – Strawberry Sep 21 '18 at 11:31
  • If you have some script to do that in PHP... I didn't know how to arrange this in SQL. Maybe PHP can help me... – V. MARTIN Sep 21 '18 at 13:59

0 Answers0