Re: How do I invert rows into columns?
Date: 1996/03/09
Message-ID: <Pine.SUN.3.91.960309132228.20814A-100000-100000-100000-100000_at_viking.iti.gov.sg>#1/1
On Wed, 6 Mar 1996, Russell Foster 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???
Using PL/SQL, you can get a list of the students and training dates, sorted by date (using order by). Then inside the PL/SQL loop, use a counter to count up to 4 (max) and display, otherwise break out of the loop, and the output is also sorted first by student, then by training dates.
Now the output is saved to a file as ASCII, and to get the output you need, the best choice will be to use shell script.
Every time a new student encountered, a new line will be echoed, other wise just append to the old line.
'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-
Peter Teoh Information Technology Institute Internet : peter_at_iti.gov.sg Science Park II Tel : 65-7705585 11 Science Park Road Fax : 65-7791827 Singapore 117685
'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*- Received on Sat Mar 09 1996 - 00:00:00 CET