Separate Values based on Delimiter in a result set for only 1 Column with 2 Columns?

Question

I have data stored the following way in [Table]:

WEEK   | Number | Restrict
2018-1 | 1      | 101;102;103
2018-2 | 2      | 101;102;104;105
...

I would like to be able to select the results of [Number] and [Restrict] for a selected week, and then split the [Restrict] into individual results in a temp table.

Based on what I've found so far, I can use the following to split the [Restrict] column into individual rows, based on this article: Break down a delimited string into a temporary table

USE [database];

DECLARE @Week nvarchar(50);
DECLARE @Restrict VARCHAR(MAX);

SET @Week = '2018-1';
SET @Restrict = (SELECT [Restrict] FROM [dbo].[Table] WHERE [Week] = @Week);

DECLARE @ExclusionData TABLE (
    [data] nvarchar(50) NULL
    )

INSERT INTO @ExclusionData(data)
SELECT data
FROM dbo.Split(@Restrict, ';') s


SELECT * FROM @ExclusionData

How could I also return the 'Number' column beside each of the 'Restrict' values?

Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Jul 07 '18 at 02:48

Brad · Accepted Answer · 2018-07-06T17:47:04.047

1

I think this will do what you need. I am pretty sure there is a better way and will think on that, but this should do it:

USE [database];

DECLARE @Week nvarchar(50);
DECLARE @Restrict VARCHAR(MAX);
DECLARE @Number INT

SET @Week = '2018-1';

-- this is assuming you can only have one row returned
SELECT @Number = Number,
@Restrict = [Restrict] 
FROM [dbo].[Table] 
WHERE [Week] = @Week

DECLARE @ExclusionData TABLE (
    [data] nvarchar(50) NULL
)

INSERT INTO @ExclusionData(data)
SELECT data
FROM dbo.Split(@Restrict, ';') s


SELECT @Number, * FROM @ExclusionData

// updated with possible solution but it is not tested If you can provide table stucture and data insert scripts for test data I can test this out. You should be able to do something like this though

select t.Number, x.Data
FROM [dbo].[Table] t
cross apply (select data FROM dbo.Split(t.[Restrict],';') ) x
WHERE [Week] = @Week

edited Jul 06 '18 at 17:47

answered Jul 06 '18 at 17:31

Brad

3,454
3
27
50

Would I then need to loop over and add to @ExclusionData for each @Number? I can have anywhere from 1-10 [Numbers] – PraiseBeExcel Jul 06 '18 at 17:33
If you can have more then 1 record/number you would have to rewrite this to do it differently, this way wouldnt work well – Brad Jul 06 '18 at 17:35
Yeah I would always have more than 1 record returned – PraiseBeExcel Jul 06 '18 at 17:36
1

If your using SQL Server 2016 there is a new function: STRING_SPLIT That will make it much easier – Brad Jul 06 '18 at 17:37
I updated my solution with possible answer, but as I noted above it is not tested see notes in solution for details – Brad Jul 06 '18 at 17:42
Looks like STRING_SPLIT isn't available in the version of SQL server this is stored on. I am using SSMS 2017 – PraiseBeExcel Jul 06 '18 at 17:44
Ok, then the server the DB is on is not 2016 or later. I think the cross apply solution I posted will work for you then. – Brad Jul 06 '18 at 17:48
Got it working using the last edit! Thank you so much – PraiseBeExcel Jul 06 '18 at 17:48
No problem, should have thought of that in the first place – Brad Jul 06 '18 at 17:49

score 0 · Answer 2 · answered Jul 06 '18 at 17:40

0

You can use cross apply :

SELECT t1.[WEEK], t1.[Number], t2.Data as Restrict
FROM table t1 CROSS APPLY 
     dbo.Split(t1.Restrict, ';') t2
WHERE . . .;

answered Jul 06 '18 at 17:40

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 3 · answered Jul 06 '18 at 17:50

My solution may be overkill for your project but ... The correct way technically would be to create another table that had a key back into the current table. Tables should never have arrays in them.
Your current table would lose Restrict column and would serve as the primary table. The second table would point back to the primary via a foreign key. Based on what you are saying, this would look identical to the primary table (Week, Number). It would also have the Restrict column in it, but only one value.
so a primary table record may be 2018-1 | 1 and the secondary table's records for that record may be 2018-1 | 1 |101, 2018-1 | 1 |102, 2018-1 | 1 |103 ... etc.
You could create a primary key on the primary table if you'd like, then use that as a foreign key into the secondary table.

With this solution you could count how many 102 records you had, etc etc, without re-parsing the table. You could also index on that field. You could add/delete child records to the parent one much easier.

Separate Values based on Delimiter in a result set for only 1 Column with 2 Columns?

3 Answers3