Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Sql problem( select statement)
Abiy Alemu wrote:
>
> Is it possible to have on one row, values from father and son tables(dept
> and emp tables for ex.)
>
> Example
>
> col1 col2 col3 col4 col5
> Accounting king clark miller
> Research Blake Ford Smith Adams
> ... and so on
>
> where Accounting and Research are from the DEPT table and the remaining
> values are from EMP table.
Here is something I kept from (I believe) a Joe Celko column in DBMS magazine. This displays names from a column in rows across the screen. The first is for 2-across, the second is for 3-across. From that, you can see how to expand it to what you want (5-across, etc.) I don't think it is possible to do a as-many-rows-as-it-needs-across version. You would have to do that programmatically.
select t1.name name1, min(t2.name) name2
from test t1, test t2
where t1.name in
(select a.name from test a, test b where a.name <= b.name group by a.name having mod( count( b.name ), 2 ) = (select mod( count(*), 2 ) from test)) and t1.name < t2.name
select t1.name name1, min(t2.name) name2, min(t2.name) name3
from test t1, test t2, test t3
where t1.name in
(select a.name from test a, test b where a.name <= b.name group by a.name having mod( count( b.name ), 3 ) = (select mod( count(*), 3 ) from test)) and t1.name < t2.name (+) and t2.name < t3.name (+)
-- Tomm Carr -- "Can you describe your assailant?" "No problem, Officer. That's exactly what I was doing when he hit me!"Received on Wed Jul 23 1997 - 00:00:00 CDT