您的风格：

with wTable as (
SELECT nameID, name, concat(group_concat( Titleid order by nameID), ",") gc -- Additional coma at the end will ensure you no errors when you will have less than 4 values per nameID
  FROM Person 
  inner join KnownForTitles using (nameid)
  group by nameID, name
)
select nameID, name,
      substring_index(gc, ",", 1) KnownForTitles1,
      replace(substring_index(gc, ",", 2), concat(substring_index(gc, ",", 1), ","), "")  KnownForTitles2,
      replace(substring_index(gc, ",", 3), concat(substring_index(gc, ",", 2), ","), "")  KnownForTitles3,
      replace(substring_index(gc, ",", 4), concat(substring_index(gc, ",", 3), ","), "")  KnownForTitles4
  from wTable;

不过，这一个看起来更干净：

with wTable as (
SELECT nameID, name, Titleid,
       ROW_NUMBER() OVER(PARTITION BY nameID, name order by Titleid) AS rn
  FROM Person 
  inner join KnownForTitles using (nameid)
)
select nameID, name,
       min(case when rn = 1 then Titleid end) as KnownForTitles1,
       min(case when rn = 2 then Titleid end) as KnownForTitles2,
       min(case when rn = 3 then Titleid end) as KnownForTitles3,
       min(case when rn = 4 then Titleid end) as KnownForTitles4
  from wTable
  group by nameID, name;