Re: Multi column sql construct

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/06/16
Message-ID: <3rsplu$b30_at_inet-nntp-gw-1.us.oracle.com>#1/1

mcblohkc_at_leonis.nus.sg (Loh Kir Chern) wrote:
>Dear World,
>
>
>I hope to construct a SQL statement that can produce a matrix sql result.
>Example I have the following table :
>
>Marks (Student Name, Subject, Marks) and wish to construct an SQL to get
>the following "matrix" output :
>
> Student 1 Student 2 Student 3 ........
>Subject 1 x1 x2 x3
>Subject 2 y1 y2 y3
>Subject 3 z1 z2 z3
> :
> :
> :
>
>Note that student Name (columns) and Subjects (Rows) changes year by year
>as students and subjects changes. I wish to spool it to a file as an
>ASCII file. I have only sql*plus as my tool.
>
>Thanks for any advice.
>
>Rgds
>Loh Kir Chern
>(Email : mcblohkc_at_leonis.nus.sg)
>

This is one such solution that uses only SQL*Plus. I simulated your table with the scott.emp table by creating a view:

create or replace view marks ( student_name, subject, marks ) as
select ename, deptno, sal
from emp
/

Then, I used the following SQL*Plus script to generate a query from the Marks table (where subect is department and marks is salary). All of the quotes and stuff are pretty important....

---

set heading off
set feedback off
set linesize 80
column oc noprint
spool tmp.sql
select 1 oc, 'select subject'
from dual
union
select distinct 2 oc, ', max(decode( student_name, ''' ||

                      student_name ||
                      ''', marks, NULL )) "' || student_name || '"'

from marks
union
select 3 oc, 'from marks group by subject;' from dual
order by 1, 2
/
spool off

---

this generated a query that looks like:

select subject

, max(decode( student_name, 'ADAMS', marks, NULL )) "ADAMS"
, max(decode( student_name, 'ALLEN', marks, NULL )) "ALLEN"
, max(decode( student_name, 'BLAKE', marks, NULL )) "BLAKE"
, max(decode( student_name, 'CLARK', marks, NULL )) "CLARK"
, max(decode( student_name, 'FORD', marks, NULL )) "FORD"
, max(decode( student_name, 'JAMES', marks, NULL )) "JAMES"
, max(decode( student_name, 'JONES', marks, NULL )) "JONES"
, max(decode( student_name, 'KING', marks, NULL )) "KING"
, max(decode( student_name, 'MARTIN', marks, NULL )) "MARTIN"
, max(decode( student_name, 'MILLER', marks, NULL )) "MILLER"
, max(decode( student_name, 'SCOTT', marks, NULL )) "SCOTT"
, max(decode( student_name, 'SMITH', marks, NULL )) "SMITH"
, max(decode( student_name, 'TURNER', marks, NULL )) "TURNER"
, max(decode( student_name, 'WARD', marks, NULL )) "WARD"

from marks group by subject;

I then ran the query using the following:

---

set heading on
set feedback on
_at_tmp

---

Note: you only need to run the script to generate the query if the list of students changes. If the list of students does not change, then the generated query can be run over and over again.

It gives output like:  

   SUBJECT ADAMS ALLEN BLAKE CLARK FORD JAMES ---------- ---------- ---------- ---------- ---------- ---------- ----------

        10                                        2450
        20       1100                                        3000
        30                  1600       2850                              950
 

3 rows selected.  

Hope this helps....

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jun 16 1995 - 00:00:00 CEST