Advanced query problem

[gwright]

I have a database of injuries, which contains an injury number, classification and dat among other things. Now an injury may be reclassified several times over its lifetime, each of whihc generates a new record in this table.

For example:

injury, classification, date (sorting by injury number)

12476, FirstAid, 04/02/2000
12476, OSHA, 05/17/2000
12476, Restrict, 05/18/2000
12477, Lost Time, 06/01/2000

The users have come with a request to see each injury on a single line on a report, with the different classifications as columns, and a date appearing in that column if the injury ever had that classification.

For example (same data as above)

Injury FA OHSA Restrict LT Fatality
12476 04/02/2000 05/17/2000 05/18/2000
12777 06/01/2000

The questions is.... How do I take normalized data, and restructure it so that I get a single row back in a query which contains the injury number, and eitehr dats or nulls for the various injury classifications (FA, OHSA, Restrict, LT, Fatal).

One added wrinkle, an injury can have the same classification more than once. (i.e. was a First Aid, then OHSA, then back to FA). In that case, the date shown on the report should be the most recent date for that classification.

I'm pretty sure this can be done using a Stored Procedure, but have no idea other than nesting them how to go about it.

The app I'm modifying is a VB6.0 app that contains a bunch of Crystal Reports (V8.5). The back-end s Oracle 8.1.6 if that makes a difference.

[Thread1]

Hmmm.. Eventhough you have created the query then I'm sure it will take you long time to execute.. This is by design.. Tsk tsk

[antares686]

Unfortunately I do not have an Oracle server here to test on. But this should be close to the answer you are looking for.

select
injury,
max(decode(classification,'FirstAid',[date]) FirstAid,
max(decode(classification,'OSHA',[date]) OSHA,
max(decode(classification,'Restrict',[date]) [Restrict],
max(decode(classification,'Lost Time',[date]) LostTime
from tbl_injuries
group by
injury