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) |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL