Oracle 11 SQL : Split 1 row into x rows and insert a new column

Question

I asked Oracle 11 SQL : Is there a way to split 1 row into x rows -- this question is very close to that but has a small twist ...

Customer asked to Split 1 row from the Oracle DB SQL into 6 rows.

Let's say, originally the SQL (complex sql with multiple joins , etc) is pulling in 9 columns:

select A, B, C, D, E, F, G, H, I  
   from X, Y, Z . . .

(but quite complex query)

1) A, B, C, D, E, F, G, H, I.

Now, customer is asking for every row returning above pattern, the new output should be like below :

1) A, B, C, 'Name for D : ', D  
2) A, B, C, 'Name for E : ', E  
3) A, B, C, 'Name for F : ', F  
4) A, B, C, 'Name for G : ', G  
5) A, B, C, 'Name for H : ', H  
6) A, B, C, 'Name for I : ', I

Basically, the 1st 3 column values will be repeated in all the 6 New Rows.
The 4th column in the new row will be a string that says what the 5th column is about.

The procedure repeats for every row in the original query.

From the earlier answer, I know unpivot can do this -- just not able to wrangle this out myself.

UPDATE:

Actually, I wasn't clear in my question that the output for Column 4 that I wanted was not a straightaway concatenation. If that was the case, I could have done it myself. These values will not be a literal concatenation of D,E,F,G,H,I.
How about the 4th column values for D,E,F,G,H,I are the follows ? : Lennon, paul McCartney, Ringo Starr, George Harrison, Pete Best, Tommy Moore

So, the output will now look like :

1) A, B, C, 'Lennon : ', D  
2) A, B, C, 'paul McCartney : ', E  
3) A, B, C, 'Ringo Starr : ', F  
4) A, B, C, 'George Harrison : ', G  
5) A, B, C, 'Pete Best : ', H  
6) A, B, C, 'Tommy Moore : ', I

I hope you get the idea that the values for the 4th column can be any string, not a derivative of the 5th column.

UPDATE2:
Suppose, my complex query, for illustration purpose, can be simplified to the classic Oracle Tables

Suppose, I run this SQL on those Emp and Dept tables :

select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal , dept.deptno, dept.dname, dept.loc from emp, dept where emp.deptno = dept.deptno;

In my case, column 4 will be "mgr", "hiredate", "sal", "deptno", "dname" and "loc".

So, for example, for the following (original) result Row from the above query :
empno, ename, job, mgr, hiredate, sal , deptno, dname, loc
7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850.00, 30, SALES, CHICAGO

The new 6 rows would be
7698 BLAKE MANAGER mgr 7839
7698 BLAKE MANAGER hiredate 1981-05-01
7698 BLAKE MANAGER sal 2850.00
7698 BLAKE MANAGER deptno 30
7698 BLAKE MANAGER dname SALES
7698 BLAKE MANAGER loc CHICAGO

What should I do to convert the above SQL to get the above new 6 Rows ?

You could give an alias to your main query or place it in a with clause, then join it to the unpivoted table you generate from it: `select main.a, main.b, main.c, 'Name for ' || unpivoted.col || ' :', unpivoted.col from main, unpivoted where unpivoted.col not in (main.a, main.b, main.c)` in this example `unpivoted` can be a subquery that gives you the unpivoting you want of `main` — grog, Dec 09 '19 at 08:31
@grog : I updated my question to clarify. Pls. take a look at the update. — anjanb, Dec 09 '19 at 17:02
I see, well, where are those values coming from then? If you have them stored somewhere (hopefully not as column names), then it would be achievable — grog, Dec 10 '19 at 08:54

thehazal · Answer 1 · 2019-12-09T21:40:46.890

4

You can use unpivot clause. I think this code will help you:

select a,b,c,'Name for ' || name_code || ' : '|| name_code as value  from 
(select 'A' a ,'B' b ,'C' c ,'D' d,'E' e,'F' f,'G' g,'H' h,'I' i from dual) 
unpivot include nulls 
(
name_for for name_code in (d as 'D', e as 'E' ,f as 'F',g as 'G',h  as 'H',i as 'I') 
);

After the update of the question. The answer is changed to this:

select A,B,C,'Name for ' || name_for  as value, name_code  from 
(select 1 A,2 B,3 C,'Lennon' D,'Paul McCartney' E, 'Ringo Starr' F, 
              null G, 'Pete Best'H, 'Tommy Moore'  I from dual )
unpivot include nulls 
(
name_for for name_code in (d,e,f,g,h,i) 
)

edited Dec 09 '19 at 21:40

answered Dec 09 '19 at 08:28

thehazal

81
5

I updated the question to clarify what was really required. – anjanb Dec 09 '19 at 15:54
i changed the answer . – thehazal Dec 09 '19 at 21:41
thanks @thehazal. Suppose, let's say, my complex query(more than 60 lines with multiple joins, etc) can be simplified to the oracle standard tables emp and dept. "select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal , dept.deptno, dept.dname, dept.loc where emp.deptno = dept.deptno" In my case, column 4 will be "mgr", "hiredate", "sal", "deptno", "dname" and "loc". Column 5 will be values for those corresponding column names. Can you show me how to replace the emp, dept query in your solution ? I have update 2 in my question now to help clarify further. – anjanb Dec 11 '19 at 04:05
added UPDATE2. Please see if you can help. – anjanb Dec 11 '19 at 05:16
added a bounty. – anjanb Dec 11 '19 at 09:08

Barbaros Özhan · Answer 2 · 2019-12-11T12:28:52.940

2

You can use unpivot with concat() function concat( concat('Name for ',val),' : ') or concatenation operators (||) as 'Name for '||val||' : ' :

with t(a,b,c,d,e,f,g,h,i) as
(
 select 1,2,3,'Lennon','Paul McCartney', 'Ringo Starr', 
              null, 'Pete Best', 'Tommy Moore' 
   from dual 
)
select a,b,c, concat( concat('Name for ',val),' : ') as explanation,
       col 
  from
  (
   select nvl(to_char(a),' ') as a, nvl(to_char(b),' ') as b,
          nvl(to_char(c),' ') as c, nvl(to_char(d),' ') as d, 
          nvl(to_char(e),' ') as e, nvl(to_char(f),' ') as f, 
          nvl(to_char(g),' ') as g, nvl(to_char(h),' ') as h, 
          nvl(to_char(i),' ') as i
    from t
  )  
  unpivot 
  ( val for col in (d,e,f,g,h,i) )
  order by col

Demo

Update 2 : Depending on your new case, the query can be rearranged as :

with t as
(
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal , 
       d.deptno, d.dname, d.loc 
  from emp e 
  join dept d
    on e.deptno = d.deptno 
)
select empno,ename,job, lower(col) as col, val 
  from
  (
   select to_char(empno) as empno, 
          ename, 
          job, to_char(mgr) as mgr, 
          to_char(hiredate,'yyyy-mm-dd') as hiredate, 
          to_char(sal,'fm999G990D00','NLS_NUMERIC_CHARACTERS = ''.,''') as sal,
          to_char(deptno) as deptno, dname, loc 
     from t
   )  
unpivot include nulls
( val for col in (mgr,hiredate,sal,deptno,dname,loc) );

Demo2

edited Dec 11 '19 at 12:28

answered Dec 09 '19 at 08:48

Barbaros Özhan

59,113
10
31
55

Hi @Barbaros Özhan, I updated the question to clarify what was really required. Can you please try now ? Thank you, – anjanb Dec 09 '19 at 15:55
well @anjanb I've edited. Both `col` and `val` should be used within SELECT list. – Barbaros Özhan Dec 09 '19 at 16:28
Thanks @Barbaros Özhan! How does this treat NULL values for D, E, F, G, H and I ? – anjanb Dec 09 '19 at 17:38
you're welcome @anjanb . Need to add `nvl()` functions individually. – Barbaros Özhan Dec 09 '19 at 17:48
1

Thanks @Barbaros Özhan! Where in your answer should I replace my complex SQL ? I had earlier replaced "select blah blah from dual" with my complex sql. Now, your latest answer's "select blah blah from dual" doesn't have any selection of d,e,f,g,h and I. So, I'm not sure how to use your solution. Thanks for your inputs so far. – anjanb Dec 09 '19 at 18:02
@anjanb unfortunately you should replace individually :) – Barbaros Özhan Dec 09 '19 at 18:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/203928/discussion-between-anjanb-and-barbaros-ozhan). – anjanb Dec 09 '19 at 18:21
added a bounty. – anjanb Dec 11 '19 at 09:08

score 2 · Answer 3 · answered Dec 11 '19 at 15:57

Just for something different, this can also be done "the old fashioned way" (i.e., without UNPIVOT).

In this approach, cross join your complex query to a row generator (SELECT rownum FROM DUAL CONNECT BY rownum <= ...). Then DECODE on the rownum from the generator. Like this:

select your_complex_query.empno, your_complex_query.ename, your_complex_query.job, 
       DECODE(rn, 1, 'mgr',
                  2, 'hiredate',
                  3, 'sal',
                  4, 'deptno',
                  5, 'dname',
                  6, 'loc',
                  '*error: unmapped*') row_description,
       DECODE(rn, 1, to_char(your_complex_query.mgr),
                  2, to_char(your_complex_query.hiredate, 'DD-MON-YYYY'),
                  3, to_char(your_complex_query.sal, 'FM999,990.00'),
                  4, to_char(your_complex_query.deptno),
                  5, your_complex_query.dname,
                  6, your_complex_query.loc,
                  '*error: unmapped*') row_value
from ( select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal , dept.deptno, dept.dname, dept.loc from scott.emp, scott.dept where emp.deptno = dept.deptno) your_complex_query, 
     ( SELECT rownum rn FROM dual CONNECT BY rownum <= 6 ) rn
;

Hopefully that's clear as to where your complex query goes.

Results:

+-------+-------+-----------+-----------------+------------+
| EMPNO | ENAME |    JOB    | ROW_DESCRIPTION | ROW_VALUE  |
+-------+-------+-----------+-----------------+------------+
|  7839 | KING  | PRESIDENT | mgr             |  -         |
|  7839 | KING  | PRESIDENT | hiredate        | 17-Nov-81  |
|  7839 | KING  | PRESIDENT | sal             | 5,000.00   |
|  7839 | KING  | PRESIDENT | deptno          | 10         |
|  7839 | KING  | PRESIDENT | dname           | ACCOUNTING |
|  7839 | KING  | PRESIDENT | loc             | NEW YORK   |
|  7698 | BLAKE | MANAGER   | mgr             | 7839       |
|  7698 | BLAKE | MANAGER   | hiredate        | 1-May-81   |
|  7698 | BLAKE | MANAGER   | sal             | 2,850.00   |
|  7698 | BLAKE | MANAGER   | deptno          | 30         |
|  7698 | BLAKE | MANAGER   | dname           | SALES      |
|  7698 | BLAKE | MANAGER   | loc             | CHICAGO    |
|  7782 | CLARK | MANAGER   | mgr             | 7839       |
|  7782 | CLARK | MANAGER   | hiredate        | 9-Jun-81   |
|  7782 | CLARK | MANAGER   | sal             | 2,450.00   |
|  7782 | CLARK | MANAGER   | deptno          | 10         |
|  7782 | CLARK | MANAGER   | dname           | ACCOUNTING |
|  7782 | CLARK | MANAGER   | loc             | NEW YORK   |
|  7566 | JONES | MANAGER   | mgr             | 7839       |
|  7566 | JONES | MANAGER   | hiredate        | 2-Apr-81   |
|  7566 | JONES | MANAGER   | sal             | 2,975.00   |
|  7566 | JONES | MANAGER   | deptno          | 20         |
|  7566 | JONES | MANAGER   | dname           | RESEARCH   |
|  7566 | JONES | MANAGER   | loc             | DALLAS     |
|  7788 | SCOTT | ANALYST   | mgr             | 7566       |
|  7788 | SCOTT | ANALYST   | hiredate        | 19-Apr-87  |
|  7788 | SCOTT | ANALYST   | sal             | 3,000.00   |
|  7788 | SCOTT | ANALYST   | deptno          | 20         |
|  7788 | SCOTT | ANALYST   | dname           | RESEARCH   |
|  7788 | SCOTT | ANALYST   | loc             | DALLAS     |
|  7902 | FORD  | ANALYST   | mgr             | 7566       |
|  7902 | FORD  | ANALYST   | hiredate        | 3-Dec-81   |
|  7902 | FORD  | ANALYST   | sal             | 3,000.00   |
|  7902 | FORD  | ANALYST   | deptno          | 20         |
|  7902 | FORD  | ANALYST   | dname           | RESEARCH   |
|  7902 | FORD  | ANALYST   | loc             | DALLAS     |
|  7369 | SMITH | CLERK     | mgr             | 7902       |
|  7369 | SMITH | CLERK     | hiredate        | 17-Dec-80  |
|  7369 | SMITH | CLERK     | sal             | 800        |
|  7369 | SMITH | CLERK     | deptno          | 20         |
|  7369 | SMITH | CLERK     | dname           | RESEARCH   |
|  7369 | SMITH | CLERK     | loc             | DALLAS     |
|  7499 | ALLEN | SALESMAN  | mgr             | 7698       |
|  7499 | ALLEN | SALESMAN  | hiredate        | 20-Feb-81  |
|  7499 | ALLEN | SALESMAN  | sal             | 1,600.00   |
|  7499 | ALLEN | SALESMAN  | deptno          | 30         |
|  7499 | ALLEN | SALESMAN  | dname           | SALES      |
|  7499 | ALLEN | SALESMAN  | loc             | CHICAGO    |
|  7521 | WARD  | SALESMAN  | mgr             | 7698       |
|  7521 | WARD  | SALESMAN  | hiredate        | 22-Feb-81  |
+-------+-------+-----------+-----------------+------------+

That was the answer I intended to write. :-) – Mehmet Kaplan Dec 14 '19 at 04:40 — Mehmet Kaplan, Dec 14 '19 at 04:40

score 2 · Answer 4 · answered Dec 11 '19 at 21:51

If results has to be materialized the easiest way is to use INSERT ALL:

INSERT ALL
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'mgr', mgr)
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'hiredate', hiredate)
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'sal', sal)
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'deptno', deptno)
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'dname', dname)
INTO t(empno, ename, job, "key", "value") VALUES (empno, ename, job, 'loc', loc)
select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal
      ,dept.deptno, dept.dname, dept.loc 
from emp
join dept 
 on emp.deptno = dept.deptno;

SELECT * FROM t;

db<>fiddle demo

Hi @Lukasz Szozda, unfortunately, I don't have liberty to create a table or view. I just get to use what's already there. — anjanb, Dec 12 '19 at 03:45
I know it doesn't satisfy the OP, but +1 for a clever trick for splitting rows. — Matthew McPeak, Dec 13 '19 at 18:48

score 0 · Answer 5 · answered Dec 11 '19 at 11:54

Regarding to UPDATE2, this seems to do return the desired output:

with o as (select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal , dept.deptno, dept.dname, dept.loc from emp, dept where emp.deptno = dept.deptno)
select o.empno, o.ename, o.job, j.column_name, j.value FROM o inner join (
 select o.empno, 'MGR'  as column_name, TO_CHAR(o.mgr)  as value  FROM o UNION ALL
 select o.empno, 'HIREDATE', TO_CHAR(o.hiredate) FROM o UNION ALL
 select o.empno, 'SAL', TO_CHAR(o.sal) FROM o UNION ALL
 select o.empno, 'DEPTNO', TO_CHAR(o.deptno) FROM o UNION ALL
 select o.empno, 'DNAME', TO_CHAR(o.dname) FROM o UNION ALL
 select o.empno, 'LOC', TO_CHAR(o.loc) FROM o) j
 on (o.empno = j.empno)
 order by o.empno;

However, you cannot use dynamic no. of columns and all the 'values' must be convertible to string. I'm not sure if you can get any further without dynamic SQL.

score 0 · Answer 6 · answered Dec 13 '19 at 16:30

And the dark side of xquery ;)

with big_query as (select 7698 empno, 'BLAKE' ename, 'MANAGER' job, 7839 mgr, to_date('1981-05-01','yyyy-mm-dd') hiredate, 2850.00 sal , 30 deptno, 'SALES' dname, 'CHICAGO' loc from dual
union all
select 7698 empno, 'BLAKE2' ename, 'MANAGER2' job, 7839 mgr, to_date('1981-05-01','yyyy-mm-dd') hiredate, 2850.00 sal , 30 deptno, 'SALES2' dname, 'CHICAGO2' loc from dual 
),
xmlwrap as (select xmlforest(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO,DNAME,LOC) xml_body from big_query)

select x.* from xmlwrap,xmltable('for $i in (4 to 9) 
                                return(
                                <ROW>
                                    <C1>{$doc/*[1]/text()}</C1>
                                    <C2>{$doc/*[2]/text()}</C2>
                                    <C3>{$doc/*[3]/text()}</C3>
                                    <C4>{concat("Name for ", $doc/*[$i]/name())}</C4>
                                    <C5>{$doc/*[$i]/text()}</C5>                                       
                                </ROW>                                                   
                                ) 
                                    ' 
                            passing xml_body as "doc"                   
                            COLUMNS 
                             "C1" varchar2(100) PATH '/ROW/C1',
                             "C2" varchar2(100) PATH '/ROW/C2',
                             "C3" varchar2(100) PATH '/ROW/C3',
                             "C4" varchar2(100) PATH '/ROW/C4',
                             "C5" varchar2(100) PATH '/ROW/C5'
                            ) x;

xmlforest - put your columns here in correct order and change big_query.

Pawan Prasad · Answer 7 · 2019-12-18T07:13:47.387

This worked for me on employees and departments hr tables But first, all the columns need to be converted to char in with clause.

This is my SQL

with a as (select emp.employee_id, emp.first_name, emp.job_id, to_char(emp.MANAGER_ID) as MANAGER_ID, 
to_char(emp.HIRE_DATE,'YYYYMMDD') as HIRE_DATE, to_char(emp.salary) as salary, 
to_char(dept.department_id) as department_id , 
to_char(dept.department_name) as department_name ,
to_char(dept.location_id) as location_id 
from HR.employees emp, HR.departments dept where emp.department_id = dept.department_id)
select * from a 
unpivot 
(Value for Detail in ( MANAGER_ID as 'MANAGER_ID',
HIRE_DATE as 'HIRE_DATE',salary as 'SALARY', 
department_id as 'department_id',
department_name as 'department_name',
location_id as 'location_id'));

Oracle 11 SQL : Split 1 row into x rows and insert a new column

7 Answers7

Linked