Re: How do I invert rows into columns?

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/03/07
Message-ID: <4hn3u3$j7q_at_inet-nntp-gw-1.us.oracle.com>#1/1

Russell Foster <Russell_Foster_at_mmacmail.jccbi.gov> wrote:

>I am trying to write a sql statement for a view that takes a table of
>class training in the structure:
 

>student_num, training_date
 

>and displays the student and last 4 times trained (there may be more or
>less than four times trained) it to the format:
 

>student_num, training_date1, training_date2, training_date3,
>training_date4
 

>I know how to get the number of times trained, and the first and the last
>times trained, with the count(*), min(*), and max(*) functions.
 

>But how do I get the last four??? This is causing a brain meltdown!!

To get the last four you would:

select student_num, training_date
  from T a
 where 4 >= ( select count(*)

                from T b
               where b.student_num = a.student_num
                 and b.training_date > a.training_date )

To do the transposition you would need to add a ranking to each row so that you could look at a row and see that it is the last date, second to last, third to last and fourth to last. That would be:

select a.student_num, a.training_date, count(*) from testing a, testing c
where 4 >= ( select count(*) from testing b where b.student_num = a.student_num

             and b.training_date >= a.training_date ) and a.student_num = c.student_num
and a.training_date <= c.training_date
group by a.student_num, a.training_date
/

Then, to transpose, we would use decode as such:

select student_num, max(t4), max(t3), max(t2), max(t1) from
(

select a.student_num,
	   decode( count(*), 1, a.training_date, NULL ) t1,
	   decode( count(*), 2, a.training_date, NULL ) t2,
	   decode( count(*), 3, a.training_date, NULL ) t3,
	   decode( count(*), 4, a.training_date, NULL ) t4

from testing a, testing c
where 4 >= ( select count(*) from testing b where b.student_num = a.student_num

             and b.training_date >= a.training_date ) and a.student_num = c.student_num
and a.training_date <= c.training_date
group by a.student_num, a.training_date
)
group by student_num
/

If you have v7.1 or above. If not, you will need to create the view:

create view student_view
as

select a.student_num,
	   decode( count(*), 1, a.training_date, NULL ) t1,
	   decode( count(*), 2, a.training_date, NULL ) t2,
	   decode( count(*), 3, a.training_date, NULL ) t3,
	   decode( count(*), 4, a.training_date, NULL ) t4

from testing a, testing c
where 4 >= ( select count(*) from testing b where b.student_num = a.student_num

             and b.training_date >= a.training_date ) and a.student_num = c.student_num
and a.training_date <= c.training_date
group by a.student_num, a.training_date

And

select student_num, max(t4), max(t3), max(t2), max(t1) from student_view
group by student_num
/

Regardless, if you have a small number of rows, the above will work. For larger tables, you may need to drop out to pl/sql and do some procedural work to get the best performance.

>Any help from you SQL jockeys out there will be greatly appreciated.
 

>Thanks.
>Russell Foster.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Thu Mar 07 1996 - 00:00:00 CET