Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Sql problem( select statement)

Re: Sql problem( select statement)

From: Tomm Carr <tommcatt_at_geocities.com>
Date: 1997/07/23
Message-ID: <33D6AA63.5E0@geocities.com>#1/1

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

group by t1.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 (+)

group by t1.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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US