Thursday, 10 October 2013

Informatica Scenarios:Pivoting of records(Pivoting of Employees by Department)

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


6 comments:

  1. Thanks, but how can you PIVOT data into separate columns, not just concatenate records into the same column?

    ReplyDelete
  2. you could select that column and separate it by "|"

    ReplyDelete
  3. Hey Ignacio,

    I've tried the same transformation but didnt get the same output. Can you please let us know if the above depicted transformation is accurate ?

    ReplyDelete
  4. SELECT department_id "Dept.",
    LISTAGG(last_name, '|') WITHIN GROUP (ORDER BY hire_date) "Employees"
    FROM employees
    GROUP BY department_id
    ORDER BY department_id;

    ReplyDelete
  5. this can be achevied by using below overrideif source is sql

    SELECT DEPTNO,REPLACE (WM_CONCAT(ENAME),',','|') ENAME
    FROM EMP
    GROUP BY DEPTNO;

    ReplyDelete
  6. please correct the following decode statement, by introduction V_EMP_PREV

    DECODE(V_DEPT_PREV,DEPT_ID,V_EMP_PREV||’|’||EMP_NAME, EMP_NAME)

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis