【Question】

Below is a piece of data queried from a database table. There are many fields in this record.
Columns: Operator1  Date1  Operator2  Date2  Operator3  Date3...
Data:    Tom      03/04/2014    Mary    09/09/2014     Bob      01/01/2015...
What I want is the following layout after executing a SQL query:
Tom     03/04/2014
Mary    09/09/2014
Bob     01/01/2015
......
The original one row is transposed to multiple rows and two columns. I wrote a SQL query to do this, but it’s too complicated. Is there any simple one? I use oracle 11g. Thanks. 

【Answer】

If there are only several columns, we can perform a union. But there are too many columns in your data, the SQL query is complicated. Suppose there are multiple tables with different columns, the query will be even more complicated. In this case, we can use SPL to do this (Suppose there is only one row in a table):

Before executing the SPL script, save the original data in the database table tb1 and configure data source connection in esProc to connect to the Oracle database. (For database configuration, see http://doc.raqsoft.com/esproc/tutorial/sjkpzh.html)

A1: oracle is the data source object. The query() function gets the to-be-processed data. (1) means getting the first row;

A2: Create a table sequence to store the final query result;

A3: Transpose the one row in A1 into multiple rows, and save the transposed data in A2.

Below is the transposition result: