MySQL layout query

Question

I have a table that is like this:

id        field_label        field_value
123       field1             abc
123       field2             bbb
123       field3             ccc
555       field1             cba
555       field2             uhu
555       field3             ddd

Now, I need it to display like this:

id        field1         field2     field3
123       abc            bbb        ccc
555       cba            uhu        ddd

Is there any way of doing this with just MySQL? I am trying to avoid running a procedure and want to display the data with just a query so that I can use a virtual table. Any help would be greatly appreciated!

Would this work for you? http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by — PinnyM, Feb 24 '12 at 18:25
This is known as a crosstab or pivot. If the number of columns in the pivoted output is unknown beforehand (a dynamic pivot) this is relatively hard to do and there are stored procedures available to do it. The best reference I've seen is http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf — Jim Garrison, Feb 24 '12 at 18:30

score 1 · Answer 1 · answered Feb 24 '12 at 18:53

If you want this to be dynamic you will have to PIVOT which I believe is not available in MySQL (I know you can do it in SQL Server 2005+), but can be accomplished with considerable effort. If you prefer a simple/dirty solution try this:

SELECT
   id,
   MAX(IF(field_label = 'field1',field_value,NULL) AS 'field1',
   MAX(IF(field_label = 'field2',field_value,NULL) AS 'field2',
   MAX(IF(field_label = 'field3',field_value,NULL) AS 'field3'
GROUP BY 
   id

The MAX will return the Last (Sorted A-Z for text fields) value but will never return NULL. You could also use MIN which would return the first value (Sorted A-Z) if there were duplicates.

MySQL layout query

1 Answers1