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.
--
/Karsten
DBA > retired > DBA
Received on Tue May 06 2003 - 17:22:55 CDT
