Split String into rows but keep associated data

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.

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.

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