How to create multiple records from comma separated list

Question

I am using SQL Server 2019 to separate comma-separated lists of values.

I have a table that looks like the following

I would like to run a query on the table produce and output that looks like this:

From what I've read StackOverflow there isn't a built in function that can split out the commas as I would like. However, I have seen the cross apply String_Split function that I think might work, but I could only get it to work on a single column.

The table structure is as follows:

CREATE TABLE neighbourhoods3.dbo.timeslots 
(
     year VARCHAR(50) NULL,
     time_of_day VARCHAR(50) NULL,
     day_of_week VARCHAR(50) NULL,
     month VARCHAR(50) NULL,
     season VARCHAR(50) NULL,
     public_holiday VARCHAR(50) NULL,
     public_holiday_minus_1 VARCHAR(50) NULL
) ON [PRIMARY]
GO

Related reading: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — GMB, Dec 27 '19 at 16:32
`I have seen the cross apply String_Split function that I think might work, but I could only get it to work on a single column` => Cross/outer apply mutliple times — Lukasz Szozda, Dec 27 '19 at 16:32
**[Join together multiple columns split by a character in SQL](https://stackoverflow.com/a/56742939/5070879)** — Lukasz Szozda, Dec 27 '19 at 16:33
Also, I'd **strongly** recommend to use the **most appropriate datatype** always - not just make all the columns `varchar(50)` because you're too lazy to think about it.... things like `Month` and `Year` are **clearly** numerical - so you should store them as such - using `INT` (instead of `VARCHAR(50)`). `time_of_day` would most probably best be a `TIME` column — marc_s, Dec 27 '19 at 16:37
I appreciate the schema is terrible, but my main focus is achieving the code I need to get the results. — Carltonp, Dec 27 '19 at 16:50
your main focus should be on fixing this schema as priority. Not adding additional code dependent on it — Martin Smith, Dec 27 '19 at 19:19

Gordon Linoff · Answer 1 · 2019-12-27T16:46:10.693

Sadly, although SQL Server now supports string_split(), it doesn't guarantee the order of the return sets.

So, I recommend an alternative method, such as a recursive CTE. Here is an example of what the code looks like:

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;

Here is a db<>fiddle.

@Lukasz, I tried applying multiple times like the following but it didnt work ```SELECT timeslots.time_of_day ,timeslots.month ,STRING_SPLIT(timeslots.season, ',') AS expr1 ,STRING_SPLIT(timeslots.public_holiday, ',') AS expr2 FROM dbo.timeslots``` — Carltonp, Dec 27 '19 at 16:49
this seems like an ideal solution, but it seems like a lot work required. TBH, if wouldn't mind if the order wasn't correct for simpler code. Therefore, if I could get the String_Spit function to work, I'd be happy with that — Carltonp, Dec 27 '19 at 16:53

score -2 · Answer 2 · edited Dec 27 '19 at 17:56

-2

SELECT GROUP_CONCAT(timeslots.year) as year,GROUP_CONCAT(timeslots.time_of_day) as time_of_day 
FROM `timeslots` 
WHERE 1 
GROUP BY year

Here GROUP_CONCAT depends on common column of group by year which used to separte by comma(,) also. Try this

edited Dec 27 '19 at 17:56

Boendal

2,496
1
23
36

answered Dec 27 '19 at 17:04

smileraj

1
3

3

GROUP_CONCAT is the opposite of what they want, and not available in MS SQL Server. – Uueerdo Dec 27 '19 at 17:05
@smileraj, I'm getting the following error: 'GROUP_CONCAT' is not a recognized built-in function name. – Carltonp Dec 27 '19 at 17:09
here 2 rows values or common so which value you want to get in single row by using Group concat .Other than long text its break the word. – smileraj Dec 27 '19 at 17:11
I have just checked out the what capabilities of the String_Function, and I don't think its possible to achieve my requirement with that function .. http://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_split-function/ Sadly, tt can only work on a single column – Carltonp Dec 27 '19 at 17:26
I tried the following as a test, but it failed ```SELECT month, public_holiday, VALUE day_of_week FROM dbo.timeslots CROSS APPLY STRING_SPLIT(timeslots.day_of_week, ',')``` – Carltonp Dec 27 '19 at 17:30

How to create multiple records from comma separated list

2 Answers2