Two rows in two columns

Question

I have table:

Owner	Book	Date
John	Vile Bodies by Evelyn Waugh	2009-05-20
Tom	Vile Bodies by Evelyn Waugh	2009-05-22
Jim	Moab is my Washpot by Stephen Fry	2009-05-26
Kate	Moab is my Washpot by Stephen Fry	2009-06-02

How can I get table:

Owner	Book	Pick_Date	Gave_Back_Book_Date
John	Vile Bodies by Evelyn Waugh	2009-05-20	2009-05-22
Tom	Vile Bodies by Evelyn Waugh	2009-05-22
Jim	Moab is my Washpot by Stephen Fry	2009-05-26	2009-06-02
Kate	Moab is my Washpot by Stephen Fry	2009-06-02

I don't get it, you just take the first next date for any book as return date of the previous owner? — PaoloJ42
– PaoloJ42, Commented Dec 7, 2021 at 11:13

user330315 · Accepted Answer · 2021-12-07 11:26:42Z

It seems you are looking for the lead() window function which allows you to access columns from the "next" row(s)

select owner, book, "date" as pick_date, lead(date) over (partition by book order by "date") as return_date from the_table order by book, "date"

Talking about the "next" row only makes sense if the rows are sorted, that's why the order by "date" is needed in the definition of the window

Paolo Castagnetti · Accepted Answer · 2021-12-07 11:17:19Z

0

You have to alter the table with

ALTER TABLE table_name ADD column_name datatype;

for adding a column, or

ALTER TABLE table_name MODIFY column_name datatype;

for modify it. Then you have to alter every line of the table that is already into it.

answered Dec 7, 2021 at 11:17

Paolo Castagnetti

13 bronze badges

2 Comments

NGT Over a year ago

Thank you for your answer. The main problem is that I want to get second table with query.

Anand Sowmithiran Over a year ago

As it is currently written, it does not answer the question. The OP was asking for a solution based on logic expressed using SQL, not the syntax to add new column to a table.

Collectives™ on Stack Overflow

Two rows in two columns

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related