Re: Help with DECODE

The cutest way of doing this is described in Tom Kyte's book (Expert one on one: Oracle - from Wrox Press).

In outline, you use the rank() analytic function (8.1.6+) partitioning over the ID column pushing it into an inline view, then apply the decode to the rank() value.

You have to know the largest number of rows per id though, or deliberately limit the number you are going to display.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Tim McNamara wrote in message ...


>I am trying to transpose the following:

>

>id         seq      comment       comment_dt

>-----    -----    ------------   ------------

>1         1          comment_1   date_1

>1         2          comment_2   date_2

>1         3          comment_3   date_3

>2         4          comment_4   date_4

>2         5          comment_5   date_5

>

>and desire to transpose the data thus (selecting the two most recent

 comment


>dates for each id):

>

>id       comment_1      comment_dt_1    comment_2    comment_dt_2

>-----  ------------      ------------         ------------   ------------

>1       comment_1       date_1                comment_2    date_2

>2       comment_3       date_3                comment_4    date_4

>

>Fumbled around with DECODE and rownum to no avail.  Could someone enlighten

>a SQL novice

>

>Much appreciated...

>

>Tim

>

>