1

Here's my problem: I want to write a query (that goes into a larger query) that takes a table like this;

ID | DATE  
A  | 1  
A  | 2  
A  | 3  
B  | 1  
B  | 2

and so on, and transforms it into;

ID | DATE1 | DATE2  
A  | 1     | 2  
A  | 2     | 3  
A  | 3     | NOW  
B  | 1     | 2  
B  | 2     | NOW

Where the numbers are dates, and NOW() is always appended to the most recent date. Given free rein I would do this in Python, but unfortunately this goes into a larger query. We're using SyBase's SQL Anywhere 12, I think? I interact with the database using SQuirreL SQL.

I'm very stumped. I thought (SQL query to transform a list of numbers into 2 columns) would help, but I'm afraid I don't know enough to make it work. I was thinking of JOINing the table to itself, but I don't know how to SELECT for only the A-1-2 rows instead of the A-1-3 rows as well, for instance, or how to insert the NOW() value into it. Does anyone have any ideas?

Community
  • 1
  • 1
yjtan
  • 193
  • 1
  • 1
  • 6
  • 1
    I would start by not doing that table structure. Instead maybe you should store dates in your date columns... – Zane Dec 23 '13 at 18:48
  • I'm not sure I really understand what you mean? The actual table itself features an ID INT column, and an ActivityDate DATE column, but I didn't want to type out everything, so I thought I'd use examples instead. – yjtan Dec 23 '13 at 19:24

1 Answers1

0

I made a an sqlfiddle.com to outline a solution for your example. You were mentioning dates, but using integers so I chose to do an integer example, but it can be modified. I wrote it in postgresql so the coalesce() function can be substituted with nvl() or similar. Also, the parameter '0' can be substituted with any value, including now(), but you must change the data type of the "i" column in the table to be a date as well. Please let me know if you need further help on this.

select a.id, a.i, coalesce(min(b.i),'0') from
test a
left join test b on b.id=a.id and a.i<b.i
group by a.id,a.i
order by a.id, a.i

http://sqlfiddle.com/#!15/f1fba/6

Joe Love
  • 5,594
  • 2
  • 20
  • 32
  • 1
    Joe Love this is amazing. I had figured out the JOIN ON the same criteria as you did, but that was as far as I got; I had no idea how to get SQL to weed out the rows I didn't want. On my test data this works exactly as I need it to! Thanks very much! – yjtan Dec 23 '13 at 21:28