SQL select joining tables

Question

If I have a table with the following data in MySQL:

id       Name       Value
1          A          4
1          A          5
1          B          8
2          C          9

how do I get it into the following format? is that even possible?

id         Column   Column   Column    Column   Column    Column
1            A        4         A        5         B        8
2            c        9

If you could explain what is your logic for this output that would be helpful. Why there is only A and C separate in one column and B in another, 4 and 9 in one column and 5 and 8 in separate columns — Avi, Aug 23 '19 at 22:26
i have a table with active projects in my work , in another table i have updates on those projects, with the columns: date and comments and im trying to view all the updates with the dates and comments in just 1 row with the id project — Moisés López Núñez, Aug 23 '19 at 22:31
Have you considered CONCAT() function? As I understand you don't have a fixed column count so if you return a string merged with a specific delimiter and parse it back in the code side could be usefull. — Sahin, Aug 23 '19 at 23:02
You could do this with a stored procedure but it would almost certainly be significantly easier on the application side. — Nick, Aug 24 '19 at 00:32
There an answer here for you please check https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — rami, Aug 24 '19 at 02:51

score 0 · Answer 1 · answered Aug 24 '19 at 12:28

You can use conditional aggregation:

select id,
       max(case when seqnum = 1 then name end) as name_1,
       max(case when seqnum = 1 then value end) as value_1,
       max(case when seqnum = 2 then name end) as name_2,
       max(case when seqnum = 2 then value end) as value_2,
       max(case when seqnum = 3 then name end) as name_3,
       max(case when seqnum = 3 then value end) as value_3
from (select t.*,
             row_number() over (partition by id order by value) as seqnum
      from t
     ) t
group by id;

SQL select joining tables

1 Answers1