In this scenario we will discuss about how
to pivot a set of records based on column:
Source Records:
Dept_id
|
Emp_name
|
10
|
CLARK
|
10
|
KING
|
10
|
MILLER
|
20
|
JONES
|
20
|
FORD
|
20
|
ADAMS
|
20
|
SMITH
|
20
|
SCOTT
|
30
|
WARD
|
30
|
TURNER
|
30
|
ALLEN
|
30
|
BLAKE
|
30
|
MARTIN
|
30
|
JAMES
|
Expected
Output
DEPT_ID
|
EMP_NAME
|
10
|
CLARK|KING|MILLER
|
20
|
JONES|FORD|ADAMS|SMITH|SCOTT
|
30
|
WARD|TURNER|ALLEN|JAMES|BLAKE|MARTIN
|
For this we
can use the below pivot_mapping. To
get the required source records we have used the below source query.
select d.deptno,e.ename from emp e,dept d where
e.deptno=d.deptno
Sort: Next
step is to sort the Deptid using a Sorter or
we can sort using the query directly.
In the
expression we need to use the logic as shown below and arrange the columns in
the below order
V_EMP_NAME = DECODE(V_DEPT_PREV,DEPT_ID,V_DEPT||’|’||EMP_NAME,
EMP_NAME)
O_EMP_NAME=V_EMP_NAME
V_DEPT_PREV=DEPT_ID
Group by
dept_id: To group the deptid colum we can make use of the AGGREGATOR .
When we group
by using the DEPTID it will group and return the last row of each department
group and the target table will have the below records as expected
SQL> SELECT * FROM PIVOT_TARGET;
DEPTN ENAME
10 CLARK|KING|MILLER
20
JONES|FORD|ADAMS|SMITH|SCOTT
30
WARD|TURNER|ALLEN|JAMES|BLAKE|MARTIN
Thanks, but how can you PIVOT data into separate columns, not just concatenate records into the same column?
ReplyDeleteyou could select that column and separate it by "|"
ReplyDeleteHey Ignacio,
ReplyDeleteI've tried the same transformation but didnt get the same output. Can you please let us know if the above depicted transformation is accurate ?
SELECT department_id "Dept.",
ReplyDeleteLISTAGG(last_name, '|') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;
this can be achevied by using below overrideif source is sql
ReplyDeleteSELECT DEPTNO,REPLACE (WM_CONCAT(ENAME),',','|') ENAME
FROM EMP
GROUP BY DEPTNO;
please correct the following decode statement, by introduction V_EMP_PREV
ReplyDeleteDECODE(V_DEPT_PREV,DEPT_ID,V_EMP_PREV||’|’||EMP_NAME, EMP_NAME)
decode(v_dept_id_prev,Dept_id,v_emp_nm|| '|' || Emp_name,Emp_name)
ReplyDelete