Re: A tricky SQL statement

This is actually a very old problem: the crosstab or pivot table query.

There are some front end tools that will do this for you Excel, Powerbuilder, etc. So if you are building an app look into the features of your tool.

As far as support in Oracle. There is none. You have two options.

You will have to hard code a query. Like Chris Halioris suggests above.

Or another alternative is to do this with dynamic SQL. Basically find the unique values of mident and then build and execute a create table statement with the dbms_sql package. Then go back through the original table and populate the dynamic table.

On Wed, 29 Jan 1997 14:17:23 +0100, Anders Hattestad <anders.hattestad_at_vegdir.vegvesen.no> wrote:

>I have a table that looks like this:
>
>sident mident dato m00
>M201 B1 07-16-95 2983
>M201 B2 07-16-95 2996
>M201 L1 07-16-95 2956
>M201 RF 07-16-95 3
>M201 VH 07-16-95 1
>M201 VR 07-16-95 33
>M251 B1 11-22-93 2555
>M251 L1 11-22-93 2535
>M251 RF 11-22-93 88
>M251 VH 11-22-93 6
>M251 VR 11-22-93 7
>M253 B1 11-30-93 2658
>
>And I would like to extract it on this form:
>
>Sident B1 B2 L1 RF
>M201 2983 2996 2956 3
>M251 2535 88
>M253 2658
>
>Have any of you a solution on how this sql statement should look like?
>
>Thanks in advances
>Anders
Received on Thu Jan 30 1997 - 00:00:00 CST