Skip to content
Advertisement

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.

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement