Calling SQL Guru's

[coolnads]

here is aproblem that i want to solve using simple oracle sql (no PLs)

there is a table with say one key (not primary), there are n rows with k rows for each key, now the maximum value of k is known. And the overall k values distributed for each key are also same ill give an example at this stage...

Col1 Col2
A 1
A 2
B 1
B 2
B 3
C 3
assuming k=3
now A can have either 1,2 or 3, but no more than 3 values that too comprising of 1 or 2 or 3 (lets say repetitions are also not allowed)

now i wnt a new table having my key as primary key and the k values in form of column for that primary key.example .(again for the above case)

Col1(Pk) Col2 Col3 Col4
A 1 2 -
B 1 2 3
C - - 3


I have been able to do this using PL/SQl or using some prog. language.
(I am actually creating some views and then using them in java)

but if the problem can be solved in simple SQL ...it would realy help plus i am definately gona learn something new.

[Schrodinger]

As you know the value of k ( the number of entries ) it may be possible. However, the SLQ will not be efficient.

I think the way would be to link the table to itself multiple times. This would produce something like :

select t1.col1, t2.col2, t3.col2, t4.col2 from table t1, table t2, table t3, table t4
where t1.col1=t2.col1(+) and t2.col2='1'(+)
and t1.col1=t3.col3(+) and t3.col2='2'(+)
and t1.col1=t4.col1(+) and t2.col2='3'(+) ;

The (+), if you haven't met them, return null if the column doesn't exist.

I'm not entirely sure that this will work, although I have done similar things. Alternatively, the other columns can be returned as subqueries :

select t1.col1, (select table.col2 from table where table.col1=t1.col1 and table.col2='1'), etc.

Whichever, you have the problem of what to do if they don't return anything. You can leave them as null, or you can use a decode to convert the nulls to something more useful.

I hope this makes sense and is of use. Do let me know how you get on.