# Intra-group Transposition

Question

i have a data table see as below .

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

i want a query that show result my data as below:

Per_Code Date In Out Break Return
1110263 2013-10-11 9:17:14 AM 6:28:55 PM 11:37:00 AM 11:38:21 AM
1110263 2013-10-11 9:17:14 AM 6:28:55 PM 1:21:30 PM 2:25:58 PM

SQL pivot can implement the transposition. But it’s hard to achieve this intra-group transposition in SQL. You can do this in SPL:

Here’s the SPL script:

 A 1 =demo.query("select   * from N100 order by PER_CODE,DATE,TIME") 2 =A1.group(PER_CODE,DATE) 3 =create(PER_CODE,DATE,In,Out,Break,Return) 4 =A2.([1,7,2,3,1,7,5,6].(A2.~(~))) 5 =A4.conj([~.PER_CODE,~.DATE]|~.(TIME).m([1,2,3,4])|[~.PER_CODE,~.DATE]|~.(TIME).m([5,6,7,8])) 6 =A3.record(A5)

A1: Retrieve data from table N100 and sort it by Per_code, Date and Time;

A2: Group data by Per_code and Date; each group includes 7 records for each person in one day;

A3: Create an empty table sequence to store the final result;

A4: Get records from each group in the order of 1,7,2,3,1,7,5,6, which are the ordered records we want in that day;

A5: Concatenate data of AM and that of PM in each record of A4 into one sequence;

A6: Populate members of each of A5’s sequences into A3’s empty table sequence.