mysql - 如何使行成为动态列？

Question

这次我遇到了一些问题要解决，所以我来了。

我在数据库中有一张名为 test 的表，包含 3 个属性(id、id_position、name)。详情请看这里

sqlfiddle

这里我们有来自 group_concat 的新结果，我给出了新的字段名称(结果)。我只想在 (id_position) 之后将 (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  +  -       +
+-------------+-----------+----------+-----------+----------+

如果结果为空，给出一个符号，比如 (-) 之类的。请给出解决此问题的建议，感谢您的宝贵时间

Answer 1

也许这不是最灵活和最漂亮的解决方案，但它适合您的情况，有 4 个位置: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;