Newsgroups: comp.databases
Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!think.com!mck-csc!
From: hin@mck-csc.mckinsey.com (John K. Hinsdale)
Subject: How do I make this class of report?
Message-ID: <1991May8.162003.15605@mck-csc.mckinsey.com>
Sender: @mck-csc.mckinsey.com
Organization: McKinsey & Co., Cambridge Systems Center
Date: Wed, 8 May 91 16:20:03 GMT

[Sorry if this is the wrong newsgroup for this]

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