Teradata Pivot

Database
Enthusiast

Teradata Pivot

I have a requirement where i need to implement pivot to achieve below result.

 

Source:

EMP_ID,Date,Seq_nbr,Punch

123,20-nov-2014,1,08:20

123,20-nov-2014,2,12:50

123,20-nov-2014,1,14:12

123,20-nov-2014,2,18:00

 

 

Target:

EMP_ID,Date,Seq_nbr,Punch_in,Punch_out

123,20-nov-2014,1,08:20,12:50

123,20-nov-2014,1,14:12,18:00

 

 

Can any one suggest on this...

1 REPLY
Senior Apprentice

Re: Teradata Pivot

Is there Seq_nbr guaranteed to be always in the correct order: 1,2,1,2,1,2,...?

SELECT 
EMP_ID,Date,Seq_nbr,
Punch AS Punch_in,
MIN(Punch) -- next value
OVER (PARTITION BY EMP_ID
ORDER BY Date, Punch
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS Punch_out
FROM tab
QUALIFY Seq_nbr = 1