Archives of the TeradataForum

Message Posted: Thu, 17 Nov 2004 @ 23:47:20 GMT

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

I'm not sure if I clearly understand your request. Are you trying to convert two rows in your source table into one row in your target?

Assuming this is what you are trying to achieve, all you need to do is a self join - or perhaps I'm don't understand your request.

It makes it hard to help unless the question, problem or need is clearly stated. On that note, there are a couple of things that are very confusing in your original question now that I study it more closely.

1) When you outline the desired outputs, do you mean when id2 instead of time when you state "... output like this for time=1"? I can't see how you could get the output you desire for the time values - assuming time is equivalent to timeframe?????

2) How are the timeframe1, timeframe2, camount1 and camount2 values calculated from the input data? At first I thought that timeframe1 and camount1 came from the corresponding values from the input record where id2 is 1. And I thought the timeframe2 and camount2 values come from the input record where the id2 value is 2. However, this doesn't work for the time=3 example.

From the information you have supplied it is difficult to provide a working example.

Anyway on the assumption your question relates to how to convert two rows into one, you can achieve that with a self join which will look something like this:

     select SRC1.id1 as tgtid1, SRC2.id2 as tgtid2,
         SRC1.timeframe as timeframe1, SRC1.amount as camount1,
         SRC2.timeframe as timeframe2, SRC2.amount as camount2
     from source_test SRC1 join source_test SRC2 on
             SRC1.id1 = SRC1.id1 and
             SRC1.timeframe = 1 and
             SRC2.timeframe = 2
     where SRC1.id2 = 2   and /* This is where your time value goes */
         SRC2.id2 = SRC1.id2

I'll leave it to you to put the actual calculations for timeframe1, timeframe2, camount1 and camount2.

I hope this helps.

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