I’m not real familiar with Split_String and my searches aren’t turning up anything that I can figure out for my case.
What I need to do is split a field into rows each time its delimited but then for each new row created, copy the associated columns with that field to those new rows.
How data will start:
Name | TimeStamp | StudentIds | Category | ReportName |
---|---|---|---|---|
Teacher Report Run | 2021-08-31 20:24:52.5430000 | 111111 | Teacher Reports | Report 1 |
Teacher Report Run | 2021-08-30 1:01:22.1250000 | 222222,333333,444444 | Teacher Reports | Report 2 |
What I need:
Name | TimeStamp | StudentIds | Category | ReportName |
---|---|---|---|---|
Teacher Report Run | 2021-08-31 20:24:52.5430000 | 111111 | Teacher Reports | Report 1 |
Teacher Report Run | 2021-08-30 1:01:22.1250000 | 222222 | Teacher Reports | Report 2 |
Teacher Report Run | 2021-08-30 1:01:22.1250000 | 333333 | Teacher Reports | Report 2 |
Teacher Report Run | 2021-08-30 1:01:22.1250000 | 444444 | Teacher Reports | Report 2 |
I was thinking Split_String but again, this is one area I haven’t had to do much of yet so I’m very green. That time has come to an end.
Advertisement
Answer
Here’s how you would do it using STRING_SPLIT
and CROSS APPLY
. Keep in mind that STRING_SPLIT
does not guarantee row ordering in the results.
x
DROP TABLE IF EXISTS #t1;
CREATE TABLE #t1(
[Name] VARCHAR(MAX),
[TimeStamp] VARCHAR(MAX),
[StudentIds] VARCHAR(MAX),
[Category] VARCHAR(MAX),
[ReportName] VARCHAR(MAX)
)
GO
INSERT INTO #t1 ([Name],[TimeStamp],[StudentIds],[Category],[ReportName])
SELECT 'Teacher Report Run', '2021-08-31 20:24:52.5430000', '111111', 'Teacher Reports', 'Report 1' UNION ALL
SELECT 'Teacher Report Run', '2021-08-30 1:01:22.1250000', '222222,333333,444444', 'Teacher Reports', 'Report 2'
SELECT #t1.Name, #t1.TimeStamp, sids.value StudentIds, #t1.Category, #t1.ReportName
FROM #t1
CROSS APPLY STRING_SPLIT(StudentIds, ',') sids