Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!cis.ohio-state.edu!tut.cis.ohio-state.edu!ucbvax!unisoft!fai!sequent!lugnut
From: lugnut@sequent.UUCP (Don Bolton)
Newsgroups: comp.databases
Subject: Re: How do I make this class of report?
Message-ID: <59812@sequent.UUCP>
Date: 10 May 91 16:28:48 GMT
References: <1991May8.162003.15605@mck-csc.mckinsey.com>
Reply-To: lugnut@sequent.UUCP (Don Bolton)
Organization: Sequent Computer Systems, Inc
Lines: 53

In article <1991May8.162003.15605@mck-csc.mckinsey.com> hin@mck-csc.mckinsey.com (John K. Hinsdale) writes:
>[Sorry if this is the wrong newsgroup for this]

ORACLE sqlplus has a function called DECODE you should check out, it should
do as you require with a bit of coding on your part.

>
>Hello netland, I have a rather simple question about how to generate a
>particular class of report from an SQL-based database (ours happens
>to be ORACLE).
>
>The problem is essentially to take a database table and generate
>output whose columns are determined by values of an element in the table.
>(What does *that* mean?)  Here's a simple example:
>
>Take a table with columns NAME, YEAR, SALARY, that looks like:
>
>NAME       YEAR   SALARY
>-----      ----   ------
>Callahan     88    20000
>Callahan     89    23000
>Callahan     90    27000
>Muldoon      88    15300
>Muldoon      89    20000
>O'Connor     89    30000
>O'Connor     90    34000
>
>I'd like to generate a report that shows the salary history
>across the page, like so:
>
>NAME            88      89      90
>------      ------  ------  ------
>Callahan     20000   23000   27000
>Muldoon      15300   20000  <null>
>O'Connor    <null>   30000   34000
>
>
>I.e., the columns in the output DEPEND ON THE DATA in the input - one
>column gets made for each unique value of the YEAR element in the input.
>
>Now, I seem to recall that doing such a thing in the FOCUS DBMS was
>pretty trivial (using its ACROSS verb).  But this appears to be a real
>pain, if not impossible, using Oracle's SQL*Plus.
>
>Anybody out there know of a better tool, or perhaps an SQL workaround
>for this kind of report?  It seems like a *really* common thing one
>needs to do.  Please E-mail, and I promise I'll post the best
>answer(s) back here in about a week.
>
>AtDhVaAnNkCsE,
>
>John K. Hinsdale
>hin@mckinsey.com