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

1

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

2

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

3

Does the data column always have home prefix?

4

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

image
image695×659 9.36 KB

5 
--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
6

Hi,

The data column always has a home prefix.

7

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

8

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