Re: seems impossible without PL/SQL

Unfortunate in your view, but did you consider that perhaps it is because this violates some of the rules for creating a Relational database. Table T is not a normalized table. Why would any RDMS developer even consider making such a think easy to do?

 Ed

Neil Zanella <nzanella_at_cs.mun.ca> wrote in message news:<Pine.LNX.4.44.0203182050160.20191-100000_at_garfield.cs.mun.ca>...
> 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
>
> a) A is the primary key of T,
>
> b) B1, ..., Bn are the n entries of table Z used as column names for T,
>
> c) for each A in X there is exactly one A as the first column of T,
>
> d) 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

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com