Pivot Query

So how to pivot data in a table?

I was asked about this by someone at work today. Let’s say you have a table that looks like following. I have included create and insert statements so that you can see it and understand it yourself.

Create table t1 (a number, b number);

insert into t1 values (1,1);

insert into t1 values (1,2);

insert into t1 values (1,3);

insert into t1 values (2,1);

insert into t1 values (2,2);

insert into t1 values (2,3);

insert into t1 values (3,1);

insert into t1 values (3,2);

insert into t1 values (3,3);

select * from t1 order by 1,2;

####

(A hint aside: You must specify the order in which you want the data to be back. It is completely wrong to say that oracle will return the data in order it was inserted.)

####

A B

----- ------

1 1

1 2

1 3

2 1

2 2

2 3

3 1

3 2

3 3

Now, let’s say you want to retrieve all the records from table t1 in a fashion where all the values in column B that belong to the group Column A should be shown side by side. Something like

A Val1 Val2 Val3

-- ---- ---- ----

1 1 2 3

One way would be to write a procedural code that stores different values of a group in an array or a series of variables and then displays them on screen or returns them to the calling program.

Or

You could just write a simple SQL statement that does it for you. You could write a piece of code that looks like this…

select

a

,max(decode(b,1,b,null)) col1

,max(decode(b,2,b,null)) col2

,max(decode(b,3,b,null)) col3

from t1

group by a;

A COL1 COL2 COL3

—– ——- ——- ——-

1 1 2 3

2 1 2 3

3 1 2 3

3 rows selected

… and you won’t have to worry about supporting an object such as a function or a procedure.

What if you had null values in one of the columns.

update t1 set b=null where a=2 and b=2;

One row updated

select

a

,max(decode(b,1,b,null)) col1

,max(decode(b,2,b,null)) col2

,max(decode(b,3,b,null)) col3

from t1

group by a;

A COL1 COL2 COL3

—– ——- ——- ——-

1 1 2 3

2 1 3

3 1 2 3

3 rows selected

There are many ways to write pivot queries. This is probably the easiest way of doing it.

Using Informatica to perform pivot operation.

You can use the Normalizer Transformation to generate rows out of columns. But to pivot values in rows into columns you would have to use Aggregator Transformation. You have to use the *FIRST* or *LAST* function to achieve this. First or last functions will pick first or last of the incoming rows if there are duplicates. Here is an example…

Suppose you have a source table with this data that is a record of monthly expenses for each of your departments.

create table deptexp (sales_id number(5), mon char(3), amt number(10,2));

insert into deptexp values (1,’JAN’,100);

insert into deptexp values (1,’FEB’,120);

insert into deptexp values (1,’MAR’,135);

insert into deptexp values (2,’JAN’,110);

insert into deptexp values (2,’FEB’,130);

insert into deptexp values (2,’MAR’,120);

Commit;

You want to load this data after denormalizing it into this structure.

create table deptexp1 (DEPT_ID number(5), JAN_AMT number(10,2), FEB_AMT number(10,2), MAR_AMT number(10,2))

The data after our process would look like…

DEPT_ID JAN_AMT FEB_AMT MAR_AMT

1 100 120 135

2 110 130 120

Do the following to accomplish this.

1. Create an Aggregator transformation with the following ports and expressions:

NAME IN/OUT EXPRESSION GROUP BY

DEPT_ID IN YES

MONTH IN NO

AMOUNT IN NO

JAN_AMT OUT FIRST(AMOUNT, MONTH='JAN')

FEB_AMT OUT FIRST(AMOUNT, MONTH='FEB')

MAR_AMT OUT FIRST(AMOUNT, MONTH='MAR')

APR_AMT OUT FIRST(AMOUNT, MONTH='APR')

2. Connect the DEPT_ID, MONTH and the AMOUNT ports from the Source Qualifier to the Aggregator.

3. Connect the JAN_AMT, FEB_AMT, MAR_AMT, etc. ports to the target.

And that would do it…