Header & Lines SQL

[aph]

Hi,

I am trying to create data file from SQL, I need file output in the below format (Header & Lines).

10 ACCOUNTING (Header)
CLARK (Line1)
KING (Line2)
MILLER (Line3)
20 RESEARCH (Header)
SMITH (Line1)
JONES (Line2)
SCOTT (Line3)
ADAMS (Line4)
FORD (Line5)

Here is the sql query I have built, but not working properly.

select d.deptno||' '||d.dname||chr(10)||e.ename
from dept d,
emp e
where d.deptno=e.deptno
and d.deptno in (10,20)
order by d.deptno

10 ACCOUNTING (Header need this)
CLARK
10 ACCOUNTING (Header don't want)
KING
10 ACCOUNTING (Header don't want)
MILLER
20 RESEARCH (Header need this)
SMITH
20 RESEARCH (Header don't want)
JONES
20 RESEARCH (Header don't want)
SCOTT
20 RESEARCH (Header don't want)
ADAMS
20 RESEARCH (Header don't want)
FORD

Thanks in advance.

[slimdave]

Use an analytical function to tell you which occurance of the deptno the row represents ...

select
   d.deptno,
   d.dname,
   e.ename,
   Row_Number() Over Partition By
      (d.deptno) dept_row
from
   dept d,
   emp e
where
   d.deptno=e.deptno and
   d.deptno in (10,20)
order by d.deptno

... then use a decode (or case, if you prefer) to include the department in only the first row of that dept's records ...

d.deptno||' '||d.dname||chr(10)||e.ename
select
   Decode(dept_row,
      1,deptno||' '||dname||chr(10))||
   ename
from
   (
   select
      d.deptno,
      d.dname,
      e.ename,
      Row_Number() Over Partition By
         (d.deptno) dept_row
   from
      dept d,
      emp e
   where
      d.deptno=e.deptno and
      d.deptno in (10,20)
   order by d.deptno
   )

Haven't tested it, but you get the idea

[aph]

Hi Slimdave,

Thanks for the help. The below portion of the query is really not clear to me. Can you specify little more what is this and how it works.


Row_Number() Over Partition By
(d.deptno) dept_row

Thanks

[slimdave]

That's an "analytic function" -- there's a section on them in the Oracle SQL Reference.

The Partition section of the Over clause defines a "window" -- in this case the result set is divided into multiple windows each identified by a unique value of deptno. Within each of these windows the Row_Number() function numbers each individual row.

So in your first set of example data the function gives you the line number in just the same way that you appended "Line1", "Line2", etc. to the various rows for each department.

Have a look at the SQL Reference for more examples.