Re: How do I invert rows into columns?

From: Gerard H. Pille <ghp_at_infosoft.be>
Date: 1996/03/09
Message-ID: <4hqj8q$eck_at_news.Belgium.EU.net>#1/1

In article <313DCB43.7BBD_at_mmacmail.jccbi.gov>, Russell_Foster_at_mmacmail.jccbi.gov says...
!>
!>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!!
!>
!>Any help from you SQL jockeys out there will be greatly appreciated.
!>
!>Thanks.
!>Russell Foster.

Easy on the poor brain now, mr. Foster.

It would be more performant if you had a separate student table, but if not:

select t1.student, t1.training_date, 
       t2.training_date,
       t3.training_date,
       t4.training_date

 from training t1, training t2, training t3, training t4  where (t1.student, t1.training_date) in

        (select student, max(training_date) from training
          group by student)
 and t2.student(+)       = t1.student

 and t2.training_date(+) < t1.training_date  and not exists (select null from training

                  where student = t2.student
                  and   training_date between t2.training_date
                                          and t1.training_date)
 and t3.student(+)       = t2.student

 and t3.training_date(+) < t2.training_date  and not exists (select null from training

                  where student = t3.student
                  and   training_date between t3.training_date
                                          and t2.training_date)
 and t4.student(+)       = t3.student

 and t4.training_date(+) < t3.training_date  and not exists (select null from training

                  where student = t4.student
                  and   training_date between t4.training_date
                                          and t3.training_date);

Sweet dreams are made of this.
Please let me know if I introduced any bugs or butterflies (it's early in the morning down here).
Have an index on student & training_date, it'll avoid any table access. Watch out for the null values you'll receive when a student has received less than 4 trainings. Received on Sat Mar 09 1996 - 00:00:00 CET