Refactoring a delimited column into a one-to-many table lookup
Note: MS SQL 2016 and above only solution below.
Dealing with a table where someone's placed multiple string values into a single varchar(max) field.
Something like this:
Id Breakdown 1 Boop 2 Scooby 3 Boop:Scooby:Shewop 4 Scooby:Boop
and on it goes. The lookup table is defined like so:
PK Name varchar(50) not null SortOrder int not null
Not sure why a PK is on a varchar(50) but that's what I'm working with. We'll be excising Breakdown on the table and getting rid of it to create a many-to-many relationship between OurData and Lookup
First step is to make sure all possible values from OurData.Breakdown are in our lookup table. Once that's done we create a table OurData_Lookup defined:
PK Id int not null identity FK OurDataId int FK LookupName varchar(50)
Now we need to transfer the data over, and why this post exists as it uses some SQL I haven't used before. Doesn't happen very often :)
INSERT INTO OurData_Lookup (OurDataId, LookupName) SELECT Id, VALUE LookupName FROM OurData CROSS APPLY string_split(OurData.Breakdown, ':')
Pretty darn slick if you ask me. From there we can remove the Breakdown column from OurData and blamo, refactor is complete