Re: SQL Reporting Problem

From: S.A.Milward <smilward_at_cen.ex.ac.uk>
Date: Fri, 3 Mar 1995 09:07:43 GMT
Message-ID: <D4uxCw.4BI_at_exeter.ac.uk>

With regard to my SQL problem where I wanted to produce a report as shown below:

Original table

   A B Dim

• -- --- 60 35 200 50 36 300 70 35 400 50 35 200 60 36 300 70 36 400 Report

    200 300 400

• --- --- A 55 55 70 B 35 36 35.5

I would like to thank all who responded, especially Paolo Bertiglia & Daniela Rea who produced the following solution:  

select column_name name,

avg(decode(dim,200,decode(column_name,'A',a,'B',b,null))) h200, 
avg(decode(dim,300,decode(column_name,'A',a,'B',b,null))) h300,
avg(decode(dim,400,decode(column_name,'A',a,'B',b,null))) h400

from TABNAME, user_tab_columns
where table_name='TABNAME'
and column_name <> 'DIM'
group by column_name;

Sue Milward
Exeter University
S.A.Milward_at_ex.ac.uk Received on Fri Mar 03 1995 - 10:07:43 CET