SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table testtable (teststring varchar(50));
insert into testtable values('ABC_DEF_GHI_JKL_MNO_PQR');
1 rows affected

select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
   cross apply string_split(teststring,'_')


rn
Street1


1
ABC

2
DEF

3
GHI

4
JKL

5
MNO

6
PQR


DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
DECLARE @colsForSelect  AS NVARCHAR(MAX)='';

with cte as
(
 select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
 cross apply string_split(teststring,'_')
)
select  @cols = STUFF((SELECT distinct ',' + quotename(rn)
            FROM cte
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
set @query = 'SELECT  ' + @cols + ' from 
(
   select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
   cross apply string_split(teststring,''_'')
) src
pivot 
(
    max(street1) for rn in (' + @cols + ')
) piv'


execute(@query)
   


1
2
3
4
5
6


ABC
DEF
GHI
JKL
MNO
PQR