CODEWITHSUNDEEP
mysql
Thilina Ranathunga
Thilina Ranathunga

Reputation: 348

Mysql Query to display row data as columns

I need to display summary of data stored in a MySQL database table

control_point_run 

run_date    control_point   duration
10/10/2016  A   100
10/10/2016  B   200
10/10/2016  C   150
10/11/2016  A   160
10/11/2016  B   220
10/11/2016  C   180
10/12/2016  A   200
10/12/2016  B   120
10/12/2016  C   180

expected output 

run_date    A   B   C
10/10/2016  100 200 150
10/11/2016  160 220 180
10/12/2016  200 120 180

Requirement image Please help me with MySQL query to get the expected result. I have no idea how to convert row data in to column headings [A,B,C].

Please note, the expected column names (A,B,C) need to be automatically generated based on the available data. the table contains more data than in this sample and more control_point values can be inserted in the future as well.

Upvotes: 0

Views: 1821

Answers (2)

Anands23
Anands23

Reputation: 780

This may be right. Pls Check this also:

 SELECT run_date,
   (SELECT duration from tablename where control_point = 'A') as 'A',
   (SELECT duration from tablename where control_point = 'B') as 'B',
   (SELECT duration from tablename where control_point = 'C') as 'C' 
 FROM tablename
 GROUP BY run_date;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Use a pivot query:

SELECT run_date,
       MAX(CASE WHEN control_point = 'A' THEN duration END) AS A,
       MAX(CASE WHEN control_point = 'B' THEN duration END) AS B,
       MAX(CASE WHEN control_point = 'C' THEN duration END) AS C
FROM yourTable
GROUP BY run_date

Upvotes: 2

Related Questions