Mysql Query to display row data as columns

Question

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.

Have you tried google?

Prasad
– Prasad

2016-11-11 06:58:13 +00:00
Commented Nov 11, 2016 at 6:58 — Prasad
– Prasad, Commented Nov 11, 2016 at 6:58

Tim Biegeleisen · Accepted Answer · 2016-11-11 06:53:56Z

2

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

answered Nov 11, 2016 at 6:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

4 Comments

Thilina Ranathunga Over a year ago

Thanks for the quick response. the situation is bit complex than the sample data. I need columns like "A,B,C" to be automatically generated according to the available data. Because those data also can be changed,

Tim Biegeleisen Over a year ago

Then you may need to use dynamic SQL in this case. Have you tried Googling around for this?

Thilina Ranathunga Over a year ago

It tried google, but not for "dynamic SQL" I did not have that idea. And no idea in creating simple search term explaining this requirement. Will try the term "dynamic SQL" also.

Tim Biegeleisen Over a year ago

Update your question with the real problem and maybe you will get an exact answer. Clearly, what you asked (and what I answered) is not this.

Anands23 · Accepted Answer · 2016-11-11 08:13:00Z

0

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;

edited Nov 11, 2016 at 8:13

answered Nov 11, 2016 at 7:52

Anands23

7909 silver badges20 bronze badges

2 Comments

Thilina Ranathunga Over a year ago

with this, the result does not display "duration".

Thilina Ranathunga Over a year ago

Thanks for the suggestion, but the result of this contains duplicate data, displays wrong duration value for the positions that should be null. and, My requirement "column names (A,B,C) need to be automatically generated based on the available data" is not available as with your suggestion, I have to write sub select statements for all column. means if there is a new control_point in the table, it will not be in the result of this query until I add sub query for that.

Collectives™ on Stack Overflow

Mysql Query to display row data as columns

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related