Re: SQL Report

Karsten Farrell <kfarrell_at_belgariad.com> wrote:

>joel-garry_at_home.com said...
>> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.19217f67491f83d898976e_at_news.la.sbcglobal.net>...
>> > nospam_at_spam.com said...
>> > > Hi..All,
>> > >
>> > > I have 2 tables employee (employeeid), dept (dept_id, ,employeeid).
>> > > I want to get a report in the following format
>> > >
>> > > <dept_id1>
>> > > employeeid1
>> > > employeeid2
>> > > ...
>> > > ...
>> > >
>> > > <dept_id2>
>> > > employeeidXX
>> > > employeeidYY
>> > > ....
>> > > ...
>> > > ..
>> > >
>> > >
>> > > Is this possible to do it in a single SQL.
>> > >
>> > > Regards,
>> > >
>> > > P
>> > Kinda hard to do in a single SQL; relatively simple in PL/SQL. In
>> > sqlplus, type this:
>>
>> I seem to recall concatenating ascii cr or lf or something, it wasn't
>> so hard.
>> But on the other hand, I've worked in so many similar-yet-different
>> languages I sometimes get confused.
>>
>> select 'x'|| chr(10)||'y'||chr(10)||'z'
>> from dual a, dual b
>> where a.dummy = b.dummy;
>>
>> If I have to make a particular data format (as opposed to a report
>> format), I usually just massage it in [gn]awk anyways.
>>
>> jg
>> --
>> @home.com is bogus.
>> http://www.signonsandiego.com/news/uniontrib/tue/business/news_1b6briefs.html
>>
>You are right in the scenario you presented. I can display the columns
>of a given row vertically by putting newlines between each column. And
>that would be the case if dept_id and emp_id were columns in the same
>table.
>
>But I don't think that will work (*if* I understood the OP) to display
>the dept_id from the dept table, followed by one or more emp_id from the
>emp table, followed by the next dept_id, and so on.

Maybe I have missed something but wouldn't this do it?

break on dept skip 1
select d.dept_id dept,e.employeeid from
dept d,employee e
where d.employeeid = e.employeeid+
order by d.dept_id;

( as a side note, if employeeid is in the dept table why join to employee?)

hth,

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Wed May 07 2003 - 08:47:04 CDT