RaqForum 28 No.
511 View  • 
     ...

Copy Rows N Times by a Condition

 
SQL(179)

【Question】

I have a table made up of 3 fields:

Name    url    number
lily    123.com    4
jack    wer3.com    3
sisi    ert34.com    1
I need to copy each row N times (N is the number value) and append an index number after the url (For each name and url). Could anyone give me a suggestion in SQL?
name    url    number
lily    123.com1    4
lily    123.com2    4
lily    123.com3    4
lily    123.com4    4
jack    wer3.com1    3
jack    wer3.com2    3
jack    wer3.com3    3
sisi    ert34.com1    1

 

【Answer】

Only a one-liner SPL (Structured Process Language) is enough if we move the source data out of the database:

A

1

$select name,url,number from   tb

2

=A1.news(number;A1.name:name,A1.url+string(~):url,A1.number:number)

A1: Retrieve data from the database table in a SQL-like statement.

A2: Create a new table consisting of 3 fields: name,url,number, copy each of A1’s row and insert N records into it according to the number value. Each new record is composed iteratively by expression “A1.name:name,A1.url+string(~):url,A1.number:number”.

You can easily embed the SPL script into a Java program. See How to Call an SPL Script in Java to learn more.


SQL(179)
 
 •  511 View  •