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 ?

Answer 1

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) 
)

Answer 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