how to make row to dynamic column?

Question

this time I have some problem to solved my case, so I am here.

I have one tables in database with name is test and include 3 attributes (id, id_position, name). For detail please look at here

sqlfiddle

Here we have new results from group_concat and I give new field name (result). I just want to make the (result) field to new dynamic column, following (id_position). Example result :

+-------------+-----------+----------+-----------+----------+
+ id_position +  result1  +  result2 + result3   + result4  + 
+-------------+-----------+----------+-----------+----------+
+  1          +  name_11  +  name_6  +  name_5   +  name_3  +
+  2          +  name_2   +  name_1  +  -        +  -       +
+  3          +  name_4   +  name_7  +  -        +  -       +
+  4          +  name_8   +  name_9  +  name_10  +  -       +
+-------------+-----------+----------+-----------+----------+

If result is empty, give a symbol like ( - ) or something. Please give your suggestion for solved this problem, thanks for your time

for some case is like this. http://stackoverflow.com/questions/1241178/mysql-rows-to-columns . but I want the results is dynamic. so please overyone :) help — Arr, Dec 19 '14 at 19:40
If short, you can't... Because no way to get different column numbers from one query in different cases.. — vp_arth, Dec 19 '14 at 19:41
@vp_arth thank you for your suggestion :). I have new some case from . http://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns . or look at sqlfiddle . http://sqlfiddle.com/#!2/70129/13 . can you compare or explain for my case please :) ? — Arr, Dec 19 '14 at 19:52
This is not sql query) this is sql builder(written on sql). It possible then, if you can build your query every time before run. — vp_arth, Dec 19 '14 at 19:57
alright @vp_arth :), no problem if like that will possible. can you help please solved for my case ? Sorry this my first time using the query for the case :). — Arr, Dec 19 '14 at 20:03
I doubt that it can be done dynamically because you want just the first name for result1 the second name for result2 for each id and so on.So a pivot is not enough.You probably can do it with laborious SUBSTRING_INDEX and GROUP_CONCAT type manipulation but this is not dynamic — Mihai, Dec 19 '14 at 20:30
I think is different case. Just look my case without the results (http://sqlfiddle.com/#!2/a3b77/12) then your case with the results is (http://sqlfiddle.com/#!2/e6595/1). Yes if our case is same for how to make row to column, but I need a dynamic results. :) sorry @BasvanDorst and thank you for your suggestion — Arr, Dec 19 '14 at 21:24
so, I have no time to finish it, but common idea to align queries to `MAX(M)` fields and `union` they all: [This fiddle](http://sqlfiddle.com/#!2/a3b77/102). Good luck :) — vp_arth, Dec 19 '14 at 21:28
@vp_arth no problem, but I am really thank you for the time and your help. very much :D — Arr, Dec 19 '14 at 21:40

score 0 · Answer 1 · answered Dec 20 '14 at 11:51

Maybe this is not the most flexible and beautiful solution, but it works fine for your situation with 4 positions: http://sqlfiddle.com/#!2/a3b77/120

SELECT 
  t1.id_position,
  (select name from test where id_position=t1.id_position limit 0,1) AS result1,
  (select name from test where id_position=t1.id_position limit 1,1) AS result2,
  (select name from test where id_position=t1.id_position limit 2,1) AS result3,
  (select name from test where id_position=t1.id_position limit 3,1) AS result4
FROM test t1
GROUP BY t1.id_position
ORDER BY t1.id_position;

how to make row to dynamic column?

1 Answers1