...

Intra-group Transposition

 
SQL(179)

【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

 

【Answer】

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.


SQL(179)
 
 •  368 View  •