Archives of the TeradataForum

Message Posted: Fri, 13 Jul 2007 @ 14:29:07 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You need to use a derived table and RANK/OVER function and then select based on CASE statements to do cross tabulation:

     sel s_rec_key, max(case rnk when 1 then stn end), max(case rnk when 2
     then stn end), max(case rnk when 3 then stn end)
     from (sel s_rec_key, e_stn as stn, rank() over (partition by s_rec_key
     order by e_dtm) as rnk from e) dt
     group by 1

The above will work for 3 separate e_stn codes. If you need more than you can add more columns. Your test data provided as two 1,BRU records with the same timestamp. I am assuming that this is an error. If there are two separate rows with the same e_stn value and you only want one, you will need to use an extra derived table to eliminate the one(s) that you don't want to appear in the final output.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>