Thanks for the question.

Asked: August 14, 2019 - 4:54 pm UTC

Last updated: August 21, 2019 - 10:09 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Connor / Chris,

Could you please have a look ate below scenario and help in building SQL:
I need to generate duplicate row based on column value in SELECT

SQL to be executed is:

select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm
from   emp
,      dept
where  dept.deptno = 10
and    dept.deptno = emp.deptno;
<code>
And its output is:
<code>
EMPNO   ENAME   HIREDATE  SAL        COMM
-----   ------  --------- ---------- ----------
7782 CLARK 09-JUN-81 (null)     (null)         
7839 KING 17-NOV-81 1          (null)
7934 MILLER 23-JAN-82 1          1
1234 STYCK 09-JUN-81 (null)     1

Based on SAL & COMM column value i need to repeat the same row:
e.g. if SAL = "1" and COMM = null then empno 7839 row should get generated once in final SQL output FLAG should be APPLY_SAL.
if SAL = "1" and COMM = "1" then empno 7934 row should get generated twice in final SQL output FLAG should be APPLY_SAL & APPLY_COMM.
if SAL = null and COMM = "1" then empno 1234 row should get generated once in final SQL output FLAG should be APPLY_COMM.

  
EMPNO   ENAME   HIREDATE   FLAG
-----   ------  ---------  ----------
7839 KING 17-NOV-81  APPLY_SAL
7934 MILLER 23-JAN-82  APPLY_SAL
7934 MILLER 23-JAN-82  APPLY_COMM
1234 STYCK 09-JUN-81  APPLY_COMM

empno 7782 row should be ignored as noth SAL & COMM are NULL

---------- Sample table structure and dummy data -------------
drop table dept purge;

drop table emp purge;

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
) ;

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),NULL,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,1000,10);
INSERT INTO EMP VALUES (1234,'STYCK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),NULL,1300,10);
COMMIT;

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/itdpbef26afb5b1zegytjz7y3

and Connor said...

If I understand your requirements, this should suffice

SQL> select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_SAL'
  2  from   emp
  3  where  sal is not null
  4  union all
  5  select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_COMM'
  6  from   emp
  7  where  emp.comm is not null
  8  order by 1;

     EMPNO ENAME      HIREDATE         SAL       COMM 'APPLY_SAL
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      17-DEC-80        800            APPLY_SAL
      7499 ALLEN      20-FEB-81       1600        300 APPLY_COMM
      7499 ALLEN      20-FEB-81       1600        300 APPLY_SAL
      7521 WARD       22-FEB-81       1250        500 APPLY_SAL
      7521 WARD       22-FEB-81       1250        500 APPLY_COMM
      7566 JONES      02-APR-81       2975            APPLY_SAL
      7654 MARTIN     28-SEP-81       1250       1400 APPLY_SAL
      7654 MARTIN     28-SEP-81       1250       1400 APPLY_COMM
      7698 BLAKE      01-MAY-81       2850            APPLY_SAL
      7782 CLARK      09-JUN-81       2450            APPLY_SAL
      7788 SCOTT      09-DEC-82       3000            APPLY_SAL
      7839 KING       17-NOV-81       5000            APPLY_SAL
      7844 TURNER     08-SEP-81       1500            APPLY_SAL
      7876 ADAMS      12-JAN-83       1100            APPLY_SAL
      7900 JAMES      03-DEC-81        950            APPLY_SAL
      7902 FORD       03-DEC-81       3000            APPLY_SAL
      7934 MILLER     23-JAN-82       1300            APPLY_SAL

17 rows selected.

or you can have some fun and games with LATERAL in more recent releases

SQL> select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, decode(x,1,'APPLY_SAL','APPLY_COMM') tag
  2  from   emp,
  3         lateral
  4         ( select level x from dual connect by level <= nvl2(emp.comm,2,1) )
  5  where  sal is not null;

     EMPNO ENAME      HIREDATE         SAL       COMM TAG
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      17-DEC-80        800            APPLY_SAL
      7499 ALLEN      20-FEB-81       1600        300 APPLY_SAL
      7499 ALLEN      20-FEB-81       1600        300 APPLY_COMM
      7521 WARD       22-FEB-81       1250        500 APPLY_SAL
      7521 WARD       22-FEB-81       1250        500 APPLY_COMM
      7566 JONES      02-APR-81       2975            APPLY_SAL
      7654 MARTIN     28-SEP-81       1250       1400 APPLY_SAL
      7654 MARTIN     28-SEP-81       1250       1400 APPLY_COMM
      7698 BLAKE      01-MAY-81       2850            APPLY_SAL
      7782 CLARK      09-JUN-81       2450            APPLY_SAL
      7788 SCOTT      09-DEC-82       3000            APPLY_SAL
      7839 KING       17-NOV-81       5000            APPLY_SAL
      7844 TURNER     08-SEP-81       1500            APPLY_SAL
      7876 ADAMS      12-JAN-83       1100            APPLY_SAL
      7900 JAMES      03-DEC-81        950            APPLY_SAL
      7902 FORD       03-DEC-81       3000            APPLY_SAL
      7934 MILLER     23-JAN-82       1300            APPLY_SAL

17 rows selected.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Connor!!

A reader, August 15, 2019 - 5:22 am UTC

This works perfectly for my requirement.
Since my actual sqey is not just single emp table and can involve multiple table joins, is there any way in 11g to avoid UNION (something like LATERAL in 11g)

August 19, 2019 - 6:40 pm UTC

Rajesh has posted a lateral equivalent for 11g.

But the other option could be a WITH, ie

with T as
 ( select ...
   from  ... complex_stuff
 )
select * from T
union/union all
select * from T where ...

which may (or may not) work just as well

for Oracle 11g approach

Rajeshwaran Jeyabal, August 19, 2019 - 8:54 am UTC

....
Since my actual sqey is not just single emp table and can involve multiple table joins, is there any way in 11g to avoid UNION (something like LATERAL in 11g)
....

Here is an approach for 11g database, using table/cast/multiset approach.

demo@PDB1> select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_SAL'
  2  from   emp
  3  where  sal is not null
  4  union all
  5  select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_COMM'
  6  from   emp
  7  where  emp.comm is not null
  8  order by 1;

     EMPNO ENAME      HIREDATE           SAL       COMM 'APPLY_SAL
---------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      17-DEC-1980        800            APPLY_SAL
      7499 ALLEN      20-FEB-1981       1600        300 APPLY_COMM
      7499 ALLEN      20-FEB-1981       1600        300 APPLY_SAL
      7521 WARD       22-FEB-1981       1250        500 APPLY_SAL
      7521 WARD       22-FEB-1981       1250        500 APPLY_COMM
      7566 JONES      02-APR-1981       2975            APPLY_SAL
      7654 MARTIN     28-SEP-1981       1250       1400 APPLY_SAL
      7654 MARTIN     28-SEP-1981       1250       1400 APPLY_COMM
      7698 BLAKE      01-MAY-1981       2850            APPLY_SAL
      7782 CLARK      09-JUN-1981       2450            APPLY_SAL
      7788 SCOTT      19-APR-0087       3000            APPLY_SAL
      7839 KING       17-NOV-1981       5000            APPLY_SAL
      7844 TURNER     08-SEP-1981       1500          0 APPLY_SAL
      7844 TURNER     08-SEP-1981       1500          0 APPLY_COMM
      7876 ADAMS      23-MAY-0087       1100            APPLY_SAL
      7900 JAMES      03-DEC-1981        950            APPLY_SAL
      7902 FORD       03-DEC-1981       3000            APPLY_SAL
      7934 MILLER     23-JAN-1982       1300            APPLY_SAL

18 rows selected.

demo@PDB1> select empno,ename,hiredate,sal,comm,
  2      case when sal is not null and comm is null then 'APPLY_SAL'
  3           when comm is not null and sal is null then 'APPLY_COMM'
  4           when sal is not null and comm is not null
  5                  and column_value = 1 then 'APPLY_SAL'
  6              else 'APPLY_COMM' end as final_result
  7  from emp ,
  8      table(cast( multiset(select level
  9      from dual
 10      connect by level <= decode(sal,null,0,1)+
 11          decode(comm,null,0,1) ) as sys.odcinumberlist)) t2
 12  where sal is not null or
 13        comm is not null
 14  order by 1
 15  /

     EMPNO ENAME      HIREDATE           SAL       COMM FINAL_RESU
---------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      17-DEC-1980        800            APPLY_SAL
      7499 ALLEN      20-FEB-1981       1600        300 APPLY_SAL
      7499 ALLEN      20-FEB-1981       1600        300 APPLY_COMM
      7521 WARD       22-FEB-1981       1250        500 APPLY_SAL
      7521 WARD       22-FEB-1981       1250        500 APPLY_COMM
      7566 JONES      02-APR-1981       2975            APPLY_SAL
      7654 MARTIN     28-SEP-1981       1250       1400 APPLY_SAL
      7654 MARTIN     28-SEP-1981       1250       1400 APPLY_COMM
      7698 BLAKE      01-MAY-1981       2850            APPLY_SAL
      7782 CLARK      09-JUN-1981       2450            APPLY_SAL
      7788 SCOTT      19-APR-0087       3000            APPLY_SAL
      7839 KING       17-NOV-1981       5000            APPLY_SAL
      7844 TURNER     08-SEP-1981       1500          0 APPLY_SAL
      7844 TURNER     08-SEP-1981       1500          0 APPLY_COMM
      7876 ADAMS      23-MAY-0087       1100            APPLY_SAL
      7900 JAMES      03-DEC-1981        950            APPLY_SAL
      7902 FORD       03-DEC-1981       3000            APPLY_SAL
      7934 MILLER     23-JAN-1982       1300            APPLY_SAL

18 rows selected.

demo@PDB1>

one more way

A reader, August 20, 2019 - 1:53 am UTC

{


select e.*
from emp e join (
    select 1 r from dual union all
    select 2 from dual
)t
on e.sal is not null and t.r<=case when e.comm is not null then 2 else 1 end
order by e.empno, t.r

}

August 20, 2019 - 9:49 am UTC

Yep, thanks for sharing.

yet one more way

A reader, August 20, 2019 - 2:43 pm UTC

SELECT *
  FROM emp e unpivot(quantity FOR "APPLY" IN
  (sal AS 'APPLY_SAL',
   comm AS 'APPLY_COMM'))
 ORDER BY 1;

August 21, 2019 - 10:09 am UTC

Nice

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor!!

for Oracle 11g approach

one more way

yet one more way

More to Explore

SQL

Generate duplicate rows

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor!!

for Oracle 11g approach

one more way

yet one more way

More to Explore

SQL