seems impossible without PL/SQL

Hello,

It is quite unfortunate that the following cannot be achieved with standard SQL. PL/SQL or some other procedural language is needed to achieve the desired effect. Suppose there are three tables:

X(A)
Y(A,B)
Z(B)

where A is the primary key of X, B is the primary key of Z, and (A,B) is the primary key of Y with Y.A referencing X.A and Y.B referencing Z.B.

It would be nice to be able to dynamically create the following table:

T(A, B1, ..., Bn) where

1. A is the primary key of T,
2. B1, ..., Bn are the n entries of table Z used as column names for T,
3. for each A in X there is exactly one A as the first column of T,
4. for each A, each Bi is 1 if (A,Bi) appears in B, otherwise (A,Bi) is 0.

I would be interested in knowing how the generation of such a table could be automated using Oracle as well as other (R)DBMSs.

Thanks!

Neil Received on Mon Mar 18 2002 - 18:36:03 CST