Re: SELECT question.

From: <rtproffitt_at_my-deja.com>
Date: Thu, 16 Mar 2000 18:54:22 GMT
Message-ID: <8araks$6j$1@nnrp1.deja.com>

Tony,
Try the following approach.....

In article <38D0C64F.14B4820C_at_lund.mail.telia.com>, plant.eng_at_lund.mail.telia.com wrote:
> I have following problem:
> Table DEPT columns DEPT_ID, DEPT_NAME
> Table EMP, columns EMP_ID, EMP_NAME, DEPT_ID
>
> What I want instead is:
> DEPT1 EMP1 DEPT1_ID EMP1_ID
> EMP2 DEPT1_ID EMP2_ID
> EMP3 DEPT1_ID EMP3_ID
> DEPT2 EMP4 DEPT2_ID EMP4_ID
> EMP5 DEPT2_ID EMP5_ID
>

SQL> create table bob
  2 (dept varchar2(10),

  3     deptid varchar2(10),
  4     emp varchar2(10),
  5     empid varchar2(10));

 [You can adapt this to your join....]

SQL> select * from bob;

DEPT DEPTID EMP EMPID
---------- ---------- ---------- ----------

DEPTA      A          Bill       877
DEPTA      A          Julie      766
DEPTA      A          Johann     655
DEPTB      B          Karen      544
DEPTB      B          Lisa       433
DEPTB      B          Radu       211

select
  Decode(DataTbl.RowCnt,

     MinTbl.MinRow, DataTbl.Dept,
     NULL)    DeptDisplay
  ,DataTbl.Dept
  ,DataTbl.Emp

  ,DataTbl.DeptId
  ,DataTbl.EmpId
From
  (-- get rownumbers in sorted order
  select Rownum RowCnt, dept, emp, deptid, empid   From (
    select Dept, emp, deptid, empid
    from bob
    group by dept, emp, deptid, empid) a   ) DataTbl,
 --
  (-- get the min pos of each major key break   select Min(Rowcnt) MinRow, Dept
  From
    (select Rownum RowCnt, dept
    From (

      select Dept, emp, deptid, empid
      from bob
      group by dept, emp, deptid, empid) a

    ) b
  Group by Dept
  ) MinTbl
Where DataTbl.Dept = MinTbl.Dept
Order by

  DataTbl.Dept,
  DataTbl.Emp,
  DataTbl.Deptid,
  DataTbl.Empid

/

DEPTDISPLA DEPT EMP DEPTID EMPID ---------- ---------- ---------- ---------- ----------

DEPTA      DEPTA      Bill       A          877
           DEPTA      Johann     A          655
           DEPTA      Julie      A          766
DEPTB      DEPTB      Karen      B          544
           DEPTB      Lisa       B          433
           DEPTB      Radu       B          211

6 rows selected.

• Explanation ==== See numbers in text below explanation 1. Get the data ordered properly 2. Add row numbers 3. Get Rownumber of beginning of each Dept block 4. Select all the data and add rownumbers to proper order 5. Join all the data with the Department minimum Row numbers. 6. Display all the data, and a formula for displaying either the department, or NULL. When the row number of the data matches the min row of the dept, the we are at the first row of new dept, so display the dept, otherise display null. 7. Whole output is ordered properly. Don't use the displayed deptartment to order, but use the real department.

select
  6. xxx, a, b, c, ...
From
 (4. select Rownum xxxxxx
  From ( 1. xxxx
  ) a
  ) DataTbl,
 --
 (3. xxx Min xxx
  From (2. xxx

      From ( 1. xxx ) a
     ) b

  Group by Dept
  ) MinTbl
Where 5. xxx=xxx
Order by 7. a,c,b,d

Hope that helps,
Robert Proffitt
Beckman Coulter
Brea California
RTProffitt AT beckman DOT com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 16 2000 - 12:54:22 CST