...

8.6 Multi-row to multi-row transposition

 
spl-cookbook(224)

Transpose multiple rows to multiple rows through combining and computing multiple records to generate multiple desired rows.
Get the daily in-and-out data of each employee according to the punch-in records. In the following daily punch-in-and-out table, there are 7 pieces of data for each card:

Per_Code in_out Date Time Type
1110263 1 2013-10-11 09:17:14.0000000 In
1110263 6 2013-10-11 11:37:00.0000000 Break
1110263 5 2013-10-11 11:38:21.0000000 Return
1110263 0 2013-10-11 11:43:21.0000000 NULL
1110263 6 2013-10-11 13:21:30.0000000 Break
1110263 5 2013-10-11 14:25:58.0000000 Return
1110263 2 2013-10-11 18:28:55.0000000 Out

The 7 pieces of data consist of one group, and we want to transpose records to get the following result:

Per_Code Date In Out Break Return
1110263 2013-10-11 9:17:14 18:28:55 11:37:00 11:38:21
1110263 2013-10-11 9:17:14 18:28:55 13:21:30 14:25:58

Though the table structure after transposition can be predefined, it is complex to obtain the structure using pivot() function. To make the process simpler, we can first create the target data structure and then fill in the data.

SPL script:

A
1 =connect(“db”).query@x(“select * from DailyTime order by Per_Code,Date,Time”)
2 =create(Per_Code,Date,In,Out,Break,Return)
3 =A1.group((#-1)\7)
4 =A3.conj([~.Per_Code,~.Date]|~.(Time).m([1,7,2,3])|[~.Per_Code,~.Date]|~.(Time).m([1,7,5,6]))
5 >A2.record(A4)

A1 Query the original table and sort data by personnel code, date and time.
A2 Create the result table sequence.
A3 Group A1 every 7 rows.
A4 Get rows from each group according to the order of 1,7, 2, 3, 1, 7, 5 and 6 to form the ordered in-and-out record of the whole day, and concatenate them into one sequence.
A5 Insert A4’s records to A2’s table sequence.
 

spl-cookbook(224)
 
 •  133 View  •