Re: seems impossible without PL/SQL

So the structure of T is dependent on the contents of Y? Yuck! I don't know of any RDBMS that can do this without a procedural wrapper or 3rd party application.

To build T you would have to basically do this

1. Determine how many B columns in T

    select max(a_cnt)
    into n
    from (select a, count(*) a_cnt from y group by a)

2) Create T(A, B1, ..., Bn) with dynamic SQL

3) Insert into T with dynamic SQL by using a cursor on

    select a, b from y order by a, b

BTW, an object view can solve a variation of this problem. Basically you can create a view T(A, table of B_t).

"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
>
Received on Thu Mar 21 2002 - 09:06:41 CST