MySQL Row to Column

Question

can any one help me on how can I create a query output using the row data on the source table as a header on my output. Please see below for illustration.

E.G.

Row Data:

+-----------+----------+
| colHeader | value    |
+-----------+----------+
| Header1   | value 1  |
+-----------+----------+
| Header2   | value 2  |
+-----------+----------+
| Header3   | value 3  |
+-----------+----------+

Output:

+-----------+-----------+-----------+
| Header1   | header2   | Header3   |
+-----------+-----------+-----------+
| Value 1   | value 2   | Value 3   |
+-----------+-----------+-----------+

Is it possible??

Here is my MySQL script. I don't think if is it the right way. Is there any idea on how could i arrive on the above output?

SELECT t1.value AS `Header1`,
       t2.value AS `Header2`,
       t3.value AS `Header3`
  FROM (SELECT * FROM table1 WHERE colHeader='Header1') t1
  JOIN (SELECT * FROM table1 WHERE colHeader='Header2'3) t2
  JOIN (SELECT * FROM table1 WHERE colHeader='Header3') t3;

yes, It is possible. Show us your code & tell us where you are going wrong, so that we can help you. — Fahim Parkar
– Fahim Parkar, Commented Jun 28, 2012 at 9:15
You could try UNION. See if that could achieve the result you want. — sel
– sel, Commented Jun 28, 2012 at 9:18
my script is being posted, it works but I don't think if is it the right way. Is there any idea? — Bryan
– Bryan, Commented Jun 28, 2012 at 9:33
sometime you have added new row this query is not efficiency..see this.. stackoverflow.com/questions/1997670/… — Vaishu
– Vaishu, Commented Jun 28, 2012 at 9:39

Fahim Parkar · Accepted Answer · 2012-06-28 10:25:49Z

11

How about this??

SELECT  
  GROUP_CONCAT(if(colHeader = 'Header 1', value, NULL)) AS 'Header 1', 
  GROUP_CONCAT(if(colHeader = 'Header 2', value, NULL)) AS 'Header 2', 
  GROUP_CONCAT(if(colHeader = 'Header 3', value, NULL)) AS 'Header 3' 
FROM myTable;

Demo

Note, you will need GROUP BY statement when there are more data of ids as shown below in Demo 2.

Demo 2

edited Jun 28, 2012 at 10:25

answered Jun 28, 2012 at 9:47

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

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

1 Comment

Kaii Over a year ago

thats a much better approach than the usual self-join

Collectives™ on Stack Overflow

MySQL Row to Column

1 Answer 1

Demo

Demo 2

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