【Question】

Here’s a table of the following structure:

ID     name    number_of_order    seoorder  

1.0001000    Diamond plate       20       seo14572 

In fact a large number of seoorders have been found by the following SQL query:

select fbillno

from seoorder t1

left join ppbom t2 on t1.finterid=t2.finterid

These are the seoorders:

seo14572 

seo14576 

seo14582 

seo14589 

Expected result:

    ID      name      number_of_order     seoorder 

1.0001000     Diamond plate         20        seo14572，seo14576，seo14582，seo14589 

Is there any idea about how to get it?

【Answer】

To convert one to multiple, you can JOIN the two tables in SQL. But, since SQL doesn’t support explicit set data type, it can’t retain the detail data in a group (it can’t help aggregate the data in each group) and thus it can’t combine multiple values into one to return. An alternative is a stored procedure, which, however, generates complicated code, too.

In this case, SPL (Structured Process Language) offers set data type to make a simple solution. Here’s the SPL script:

A1,A2: Retrieve data from two tables respectively in SQL;

A3: Perform distinct with id() function; concat@c() converts seoorders into a sequence in which members are separated by comma.

Below is the final result: