如何从逗号分隔的列表中创建多个记录

遗憾的是，尽管SQL Server现在支持string_split()，但不能保证返回集的顺序。

因此，我推荐一种替代方法，例如递归CTE。这是代码示例的示例：

with cte as (
      select convert(varchar(max), NULL) as year,
             convert(varchar(max), NULL) as time_of_day,
             convert(varchar(max), NULL) as day_of_week,
             convert(varchar(max), year) as year_rest,
             convert(varchar(max), time_of_day) as time_of_day_rest,
             convert(varchar(max), day_of_week) as day_of_week_rest
      from t
      union all
      select convert(varchar(max), left(year_rest, charindex(',', year_rest + ',') - 1)) as year,
             convert(varchar(max), left(time_of_day_rest, charindex(',', time_of_day_rest + ',') - 1)) as time_of_day,
             convert(varchar(max), left(day_of_week_rest, charindex(',', day_of_week_rest + ',') - 1)) as day_of_week,
             stuff(year_rest, 1, charindex(',', year_rest + ','), '') as year_rest,
             stuff(time_of_day_rest, 1, charindex(',', time_of_day_rest + ','), '') as time_of_day_rest,
             stuff(day_of_week_rest, 1, charindex(',', day_of_week_rest + ','), '') as day_of_week_rest
      from cte
      where year_rest <> ''
     )
select year, time_of_day, day_of_week
from cte
where year is not null;

这是db <>