【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: