粤磊 informatica powercenter学习笔记（五）

以前在做DBA时在DB里写过行转列，列转行的CODE.这两天做了一下测试用INFORMATICA来实现行列互换的功能。

列转行的SQL 实现

ENV: RMDB

TABLE： SALES

  STORENAME QUARTER1  QUARTER2 QUARTER3 QUARTER4

  STORE1                   100                  300                  500                  700

  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

 

 

列转行的INFORMATICA 实现

 

1 SOURCE  TABLES 导入MAPING

2 SQLQUALIFER 读取数据

3 NORMALIZER 实现对应的列行转换，在NORMALIZER属性里设置STORENAME,QUARTER DATA(为LEVEL 1， 每季度的销售值SALES为LEVEL2. QUARTER DATA的OCCURS值设为4就可实现列行转换

 

 

二  行转列的SQL 实现

ENV:

create table sales2(storename varchar(20),

sales number(9),quarter number(9))

select * from sales2

SOLUTION:

SELECT STORENAME,

 MAX(CASE WHEN QUARTER=1 THEN SALES ELSE 0 END) QUARTER1,

 MAX(CASE WHEN QUARTER=2 THEN SALES ELSE 0 END) QUARTER2,

 MAX(CASE WHEN QUARTER=3 THEN SALES ELSE 0 END) QUARTER3,

 MAX(CASE WHEN QUARTER=4 THEN SALES ELSE 0 END) QUARTER4

 FROM SALES2

 GROUP BY STORENAME

 ORDER BY STORENAME

INFORMATICA 实现

1        导入源表SALES2

2        SQLQULIFER读取数据

3        用EXPERSSION TRANSFORMATION 来判断QUARTER值来取SALES值

   4   用聚合函数来取出判断后的最大值，即真正的SALES值

   

5 导入到目标表或目标文件后即实现了行转列的功能。

 

 

附件是我在DB及INFORMATICA上做的测试截屏，需要的话可以参考一下，呵呵。

.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22377317/viewspace-678652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22377317/viewspace-678652/