粤磊 informatica powercenter学习笔记（五）

列转行的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就可实现列行转换

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

.