Add column with following values from another column

Analytics
Enthusiast

Add column with following values from another column

Is there a way to create a column based on the all the following values from another column without having join the table to itself?

 

Current data:

ID      Col 1
572    8,657
572   11,559
572   19,147

 

New data:
ID     Col 1     Col2
572   8,657    11,559
572   8,657    19,147
572  11,559   19,147
572  19,147

 

2 REPLIES
Teradata Employee

Re: Add column with following values from another column

Since you are increasing the number of rows, this is in effect a cartesian join of Col1 to itself within each ID.  If you were just adding the "next" Col1 in Col2, then you could use a windowing function like Max(Col1) over(partition by ID order by Col1 rows between 1 following and 1 following).  Even that in essence would be a 1-1 join, although not written like a join.

Teradata Employee

Re: Add column with following values from another column

It would be very helpful to have the rules describing how to get to the result rows from the source rows. It is not obvious how the numbers in the third column are a calculated or what causes the extra row to be created.