Archives of the TeradataForum

Message Posted: Fri, 13 Jul 2007 @ 09:19:01 GMT

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

<-- Anonymously Posted: Friday, July 13, 2007 03:19 -->

I have 2 tables

     Table S
     (S_rec_key  decimal (15),
      S_route    varchar (50,0)
     )
     primary index (s_rec_key)

     Table E
     (E_rec_key  decimal (15,0),
      S_rec_key  decimal(15,0),
      E_stn      varchar (3,0),
      E_dtm      timestamp
     )

Table S contains information on the account and table E contains multiple events for account. What I would like is that the field S.S_route in order of the E.E_dtm. Two events following each other with the same E.E_stn are considered as one.

e.g.

     S values (1,NULL)
     S values (2, NULL)
     E values (5,1,'BRU',TIMESTAMP '20070712 12:12:12')
     E values (5,2,'AMS',TIMESTAMP '20070711 10:12:12')
     E values (5,1,'BRU',TIMESTAMP '20070712 12:11:12')
     E values (5,1,'FRA',TIMESTAMP '20070713 12:12:12')
     E values (5,2,'CDG',TIMESTAMP '20070710 12:12:12')
     E values (5,2,'HKG',TIMESTAMP '20070713 09:12:12')

I want a query that has as result that S looks like this:

     1|'BRU,FRA'
     2|'CGD,AMS,HKG'

Is this possible in pure sql?

Thanks

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