i want to convert rows into columns using PIVOT function.
Example is below
Table
EMP No EMP Name
------- --------
1 ABC
2 PQR
Output should be
Col1 Col2
---- ----
Emp No 1
Emp Name ABC
EMP No 2
Emp Name PQR
I am ok with loop and all however we should have used PIVOT, have serached google however has not got anything matching.
Please suggest and send some sample code.
Actually for you requirement, you need unpivot, not pivot. But for that, datatype of both columns should be same, character in this case
with tab(emp_no, emp_name) as (
select '1' ,'abc' from dual union all
select '2' ,'PQR' from dual)
----
--End of Data Perparation
----
select *
from tab
unpivot (col2 for col1 in ( emp_no as 'EMP No', emp_name as 'Emp Name'));
Output
| COL1 | COL2 |
|----------|------|
| EMP No | 1 |
| Emp Name | abc |
| EMP No | 2 |
| Emp Name | PQR |
I'm not sure if this can be done using PIVOT. You can select the columns separately and UNION them.
select 'Emp No' col1, emp_no col2 from tab
union all
select 'Emp Name', emp_name from tab;
Note that, both the columns should be of same datatype. Else you need to cast/convert one of them.
Also, the result may not be in the order you want. You need to sepecify the order by clause explicitly.
select col1, col2 from(
select 'Emp No' col1, emp_no col2, emp_no from tab
union all
select 'Emp Name', emp_name, emp_no from tab
)
order by emp_no, case col1 when 'Emp No' then 1 else 2 end;
