SQLTeam.com | Weblogs | Forums

Split data from one row into separate rows and add two columns

Any assistance with the following would be greatly appreciated.

drop table #sampledata
create table #sampledata
(
id int ,
data1 varchar(50),
)
go

insert into #sampledata values
(1,'home14_01,home15_01,home15_02,home15_03'),
(2,'home14_01,home15_01'),
(3, 'home15_01'),
(4, 'home14_01'),
(5, 'home13_01'),
(6, null)
select * from #sampledata

Evaluate each row above for the following conditions and then create one new row with two extra columns named data2 and data3 each time a condition is met. The compatibility level of the database is 110 which means I won't be able to use STRING_SPLIT. The end state of this will be to "Union All" the results to another set of results.

if data1 like '%13%' THEN '13a', 'aa', 'null'
if data1 like '%14%' THEN '14a', 'aa', 'null'
if data1 like '%15%' THEN '15a', 'aa', 'null'

Results

id data1 data2 data3
1 14a aa null
1 15a aa null
2 14a aa null
2 15a aa null
3 15a aa null
4 14a aa null
5 13a aa null

hi

please see if this link helps

hope you can follow and do .. if not please let me know .. i will do it for you

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

https://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html

Does the data column always have home prefix?

one idea ..dont know if it matches your requirement

select data1 , case when data1 like '%14%' then 14 end from #sampledata
union all
select data1 , case when data1 like '%15%' then 15 end from #sampledata

--INSERT INTO #newtable ( id, data1, data2, data3 )
SELECT ca1.*
FROM #sampledata sd
CROSS APPLY (
    SELECT sd.id, '13a' AS data1, 'aa' AS data2, 'null' AS data3
    WHERE sd.data1 LIKE '%13%'
    UNION ALL
    SELECT sd.id, '14a', 'aa', 'null'
    WHERE sd.data1 LIKE '%14%'
    UNION ALL
    SELECT sd.id, '15a', 'aa', 'null'
    WHERE sd.data1 LIKE '%15%'
) AS ca1
2 Likes

Hi,

The data column always has a home prefix.

Very much appreciate the assistance with this. The UNION ALLs inside the CROSS APPLY works well in this scenario. Thank you.

dynamic, in case there are other values other than 13, 14, 15



insert into #sampledata values
(1,'home14_01,home15_01,home15_02,home15_03'),
(2,'home14_01,home15_01'),
(3, 'home15_01'),
(4, 'home14_01'),
(5, 'home13_01'),
(6, null),
(7, 'home222_01')

;with src
as
(
	select * 
	  from #sampledata a
	  cross apply DelimitedSplit8K(a.data1, ',') x
	  where a.data1 is not null
)

select distinct id,  
       concat(replace(left(Item, CHARINDEX('_',Item,0) -1 ), 'home', ''), 'a') data1 , 
	   'aa' data2 , 
	   null data3
  From src a

2 Likes