String Split – For Value Copy

Question
I have a data table TEST, and I want to convert the first row into the form of rows after it.

 

RoomNo ContractNo
401, 406, 408, 411, 412, 413, 416 2013-15
401 2013-15
406 2013-15
408 2013-15
411 2013-15
412 2013-15
413 2013-15
416 2013-15

Here’s a solution:

create table TEST
(RoomNo nvarchar(555),
ContractNo nvarchar(55))
go

Insert into TEST
select‘401,406, 408,411,412,413,416’,‘2013-15’
go

 

Answer

If you don’t want a workaround in SQL, you can hack it in SPL (Structured Process Language) with a one-liner:

A

1

$select * from TEST

2

=A1.news(RoomNo.split("");~:   RoomNo, ContractNo)