Duplicate row based in column value

Question

Please I need to duplicate row when having multiple values in one column. Exemple :

A  B  12  I76J-I76H=>
A  B  12  I76J
A  B  12  I76H

Thank you .

Answer 1

Credit where credit is due big thanks to user @RichardTheKiwi for providing the majority of the code HERE

Anyway here is the code. If you want to change the sql so that it doesn't separate based upon - just replace all - with the char of you choice.

Creating the table:

    create table Testdata(Data1 varchar(50), Data2 varchar(50), Data3 int, Data4 varchar(max))
    insert Testdata select 'A', 'E', '9', 'I76J-I76H-I76I-I76G'
    insert Testdata select 'B', 'F', '8', 'I76J-I76H-I76I'
    insert Testdata select 'C', 'G', '7', 'I76J-I76H'
    insert Testdata select 'D', 'H', '6', 'I76J'

Now updating the original table TestData

;with tmp(Data1, Data2, Data3, DataItem, Data4) as (
select Data1, Data2, Data3, LEFT(Data4, CHARINDEX('-',Data4+'-')-1),
    STUFF(Data4, 1, CHARINDEX('-',Data4+'-'), '')
from Testdata
union all
select Data1, Data2, Data3, LEFT(Data4, CHARINDEX('-',Data4+'-')-1),
    STUFF(Data4, 1, CHARINDEX('-',Data4+'-'), '')
from tmp
where Data4 > ''
)
INSERT INTO Testdata
select Data1, Data2, Data3, DataItem AS Data4
from tmp
order by Data1

DELETE FROM Testdata
where Data4 like '%-%'

select * FROM TESTDATA