-
Header & Lines SQL
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.
-
Use an analytical function to tell you which occurance of the deptno the row represents ...
Code:
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 ...
Code:
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
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|