Re: SQL help needed

From: Noodles <noodles_at_aol.com>
Date: 1997/11/03
Message-ID: <19971103012000.UAA24296@ladder02.news.aol.com>#1/1

Read the below. I think it might have what you want.

BEWARE: I've never tested it and I'm not sure whether it even works.

Cliff

---

Subject: Re: Sql problem( select statement) From: Tomm Carr <tommcatt_at_geocities.com> Date: Wed, 23 Jul 1997 20:08:07 -0500
Message-ID: <33D6AAF7.28DA_at_geocities.com>

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 tion of the table name in the first

 column of your sample output
throws a monkey wrench into the problem. Do without.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"

Received on Mon Nov 03 1997 - 00:00:00 CST