Informatica PowerCenter Study Notes (iii)

I used to write a line-and-go column in db when I was working as a DBA. These two days did a test with Informatica to achieve the function of the column interchange.

The SQL implementation of the column career

Env:rmdb

TABLE: SALES

STORENAME QUARTER1 QUARTER2 QUARTER3 QUARTER4

  store1                    100                   300                   500                  

STORE2 200 400 600 800

Solution:

SELECT Storename,quarter1 as SALES, 1 as QUARTER

From SALES

GROUP by Storename,quarter1

UNION

SELECT Storename,quarter2 as SALES, 2 as QUARTER

From SALES

GROUP by Storename,quarter2

UNION

SELECT Storename,quarter3 as SALES, 3 as QUARTER

From SALES

GROUP by Storename,quarter3

UNION

SELECT Storename,quarter4 as SALES, 4 as QUARTER

From SALES

GROUP by Storename,quarter4

The INFORMATICA implementation of a career change

1 SOURCE TABLES import maping

2 Sqlqualifer reading data

3 Normalizer to achieve the corresponding column lines conversion, set storename,quarter DATA in the normalizer attribute (for level 1, quarterly sales value SALES is LEVEL2. QUARTER DATA with the occurs value set to 4 for column row conversions

SQL implementation of two rows of columns

Env:

CREATE TABLE Sales2 (storename varchar (20),

Sales number (9), Quarter Number (9))

SELECT * FROM Sales2

Solution:

SELECT STORENAME,

MAX (case if Quarter=1 then SALES ELSE 0 END) QUARTER1,

MAX (case if quarter=2 then SALES ELSE 0 END) QUARTER2,

MAX (case if Quarter=3 then SALES ELSE 0 END) QUARTER3,

MAX (case if quarter=4 then SALES ELSE 0 END) QUARTER4

From SALES2

GROUP by STORENAME

ORDER by STORENAME

INFORMATICA implementation

1 Importing source Tables SALES2

2 sqlqulifer reading data

3 use experssion transformation to judge quarter value to fetch the value of sales

4 Use the aggregate function to remove the maximum value after the judgment, that is, the true sales value

5 The function of row-to-column is implemented after importing to the target table or target file.

Informatica PowerCenter Study Notes (iii)