Mysql dynamic rows to columns

Question

layout

https://www.dropbox.com/s/6fu81cqsl25vksd/layout.png?dl=0

link to image

Example:

Create Statement:

create table rating
(
  id int,
  tieid int,
  inspected datetime,
  rate varchar(50)
);

Insert Statement:

insert into rating values
(1,1, '0000-00-00 00:00:00', 'E'),
(2,1, '2015-03-31 01:01:22', 'G'),
(3,1, '2015-02-26 01:01:22', 'B'),
(4,2, '0000-00-00 00:00:00', 'E'),
(5,2, '2015-03-31 01:01:22', 'F');

What Im trying to do is get an ouput with this data like such cant seem to figure out how to get the query correct for this

tieid | inspected1 | rate1| inspected2 | rate2| inspected3 | rate3|
1 | 0000-00-00 |E |2015-02-26 |B | 2015-03-31 |G |
2 | 0000-00-00 |E |2015-03-31 |F | | |
-------------------------------------------------------------------

I would say the table design is not good if this is the expected output. You may want to consider sub-queries, but they would be fragile if more inspections were added. — Jay Blanchard, Apr 08 '15 at 18:37
is tieid a reference to another object in the same rating table? I don't understand your goal here, I don't even understand the relation between your expected result and your create table/values. — Felype, Apr 08 '15 at 18:43
what im trying to do is make a pivot table with all the ratings for each tie — Zechariah Rasmussen, Apr 08 '15 at 18:49
jay, is there a better way to structure the layout. I need to keep the history of every inspection. — Zechariah Rasmussen, Apr 08 '15 at 18:50
doesnt `select * from ratings where tieid=$d` do something... close to that? Edit: ok i think i get it, but if i'm right you're trying to group by tieid, while still selecting each individual row but turning those rows per tie into columns. I'm afraid this is not possible, still thinking a bit though. — Felype, Apr 08 '15 at 18:50
i guess what im asking is how to set up a procedural call correctly for this — Zechariah Rasmussen, Apr 08 '15 at 18:55

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

doing this in a single query, is not really possible in MySQL, because mySQL does not have anything to feature something like that.

on this page there's a suggestion that this might be doable using prepared statements, and dynamic query generations: MySQL pivot table query with dynamic columns

In my opinion, this approach is pretty WTFey and personally I would not use it and take a multiple query approach instead.

edited May 23 '17 at 11:56

Community

1
1

answered Apr 08 '15 at 19:33

Matthias

2,622
1
18
29

how would you suggest breaking this up into multiple queries? – Zechariah Rasmussen Apr 09 '15 at 15:02

Mysql dynamic rows to columns

1 Answers1