Home » SQL & PL/SQL » SQL & PL/SQL » Select
Select [message #265203] Wed, 05 September 2007 14:16 Go to next message
kalkumar
Messages: 8
Registered: July 2007
Junior Member
Hi,
I have tables like this:
Table1:
Year | ID | col1
2007 | 1 | x

Table2:
Year | ID | Date
2007 | 1 | 09/2/2007
2007 | 1 | 09/1/2006

Now I need to get the result like this in one row:
Year | ID | Date1 | Date2
2007 | 1 | 09/2/2007 | 09/1/2006

For each year there are two different dates only so I need Date1 and Date2 only.
How to get the result from two rows into one row.

Thanks in advance

Re: Select [message #265207 is a reply to message #265203] Wed, 05 September 2007 14:28 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
If you are 100% sure there are only two dates per year, you could do something like:
SQL> SELECT t.year_col
  2        ,t.id_col
  3        ,MIN(t.date_col)
  4        ,MAX(t.date_col)
  5  FROM   table2 t
  6  GROUP  BY t.year_col
  7           ,t.id_col;

  YEAR_COL     ID_COL MIN(T.DATE_COL) MAX(T.DATE_COL)
---------- ---------- --------------- ---------------
      2007          1 9-1-2006        9-2-2007
(reverse the order of columns if you like).

If in real life: try to avoid reserved words like year, id and date as column names... That's why I have renamed your columns in this example.
Re: Select [message #265208 is a reply to message #265203] Wed, 05 September 2007 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still refuse to follow the guidelines.
You will not have help from me.

Regards
Michel
Re: Select [message #265210 is a reply to message #265208] Wed, 05 September 2007 14:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Oops, Michel, sorry I interrupted your noble mission, didn't mean to Wink I don't keep track of people following guidelines... Let's state clearly I do think they should!

@OP: if you format your post and provide us with create table and insert scripts, you have a far better chance of getting help.
Re: Select [message #265221 is a reply to message #265210] Wed, 05 September 2007 15:55 Go to previous messageGo to next message
kalkumar
Messages: 8
Registered: July 2007
Junior Member
Hi,
I need to transform rows into columns not to get max and min dates.
Thanks
Re: Select [message #265222 is a reply to message #265203] Wed, 05 September 2007 15:58 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Search for "pivot query"
Re: Select [message #265283 is a reply to message #265210] Thu, 06 September 2007 00:46 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
skooman wrote on Wed, 05 September 2007 21:33
Oops, Michel, sorry I interrupted your noble mission, didn't mean to Wink I don't keep track of people following guidelines... Let's state clearly I do think they should!

No problem, I saw that you posted when I was writing.
I don't know if it is a "noble" mission but it is a boring one.

Regards
Michel

Previous Topic: Display single digit numbers in double digits (Merged)
Next Topic: Not in Vs Not exists
Goto Forum:
  


Current Time: Sat Dec 03 17:59:29 CST 2016

Total time taken to generate the page: 0.17957 seconds