How do I split my data into separate columns by comma separated values-CodePudding

I'm trying to split the KnownForTitles column into 4 separate columns but can't get my queries to work.

I've tried using substring with instr to select results before the comma , can you see where i'm going wrong

Select  nameID, name, 
substring_index(group_concat( Titleid order by nameID), ",", 1) KnownForTitles1,
substring(substring_index(group_concat( Titleid order by nameID), ",", 2), instr(substring_index(group_concat( Titleid order by nameID), ",",2)),",")
KnownForTitles2 

from Person
inner join KnownForTitles using (nameid)
inner Join Media using (titleID)
group by nameID
order by nameID

This is the result format I need:

CodePudding user response：

Your style:

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;

However, this one looks like cleaner:

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;