这个答案是对@timbiegeleisen答案的一个小小升级。
如果table很大，你也需要用

SET SESSION group_concat_max_len = @@max_allowed_packet;

此查询将从 GROUP_CONCAT 通过使用嵌套 SUBSTRING_INDEX 功能。
查询

SELECT 
   ID
 , SUBSTRING_INDEX(SUBSTRING_INDEX(val, ',', 1), ',', -1) AS r1
 , SUBSTRING_INDEX(SUBSTRING_INDEX(val, ',', 2), ',', -1) AS r2
 , SUBSTRING_INDEX(SUBSTRING_INDEX(val, ',', 3), ',', -1) AS r3
 , SUBSTRING_INDEX(SUBSTRING_INDEX(val, ',', 4), ',', -1) AS r4
FROM (

  SELECT
      ID, GROUP_CONCAT(val ORDER BY val) val
  FROM
  (
      SELECT ID, R1 AS val FROM yourTable
      UNION ALL
      SELECT ID, R2 FROM yourTable
  ) t
  GROUP BY ID
) x

请参见演示http://rextester.com/sdf72100

这很棘手，因为表中没有足够的ID。一种方法是使用变量，添加序列号，然后聚合：

select id,
       max(case when rn = 1 then r1 end) as r1,
       max(case when rn = 1 then r2 end) as r2,
       max(case when rn = 2 then r1 end) as r3,
       max(case when rn = 2 then r2 end) as r4
from (select t.*,
             (@rn := if(@i = id, @rn + 1,
                        if(@i := id, 1, 1)
                       )
             ) as rn
      from (select t.*
            from t
            order by t.id
           ) t cross join
           (select @rn := 0, @i := -1) params
     ) t
group by id;

我将提供一个查询，它的行为与您想要的几乎相同，而且非常简单：

SELECT
    ID, GROUP_CONCAT(val ORDER BY val) val
FROM
(
    SELECT ID, R1 AS val FROM yourTable
    UNION ALL
    SELECT ID, R2 FROM yourTable
) t
GROUP BY ID;

演示

这种方法之所以可取，有几个原因。首先，它对给定的任意数量的“列”都是健壮的 ID 可能有。第二，它提供了按任何方式对每行值排序的选项。最后，它将比使用会话变量来模拟行数之类的内容来维护精确答案要容易得多。