第二天实验全解行转列

1、行转列：建表语句：CREATE TABLE people_info ( id number(4), infotype varchar2(10), info varchar2(25) );CREATE TABLE t_people_info1 ( id number(4), name varchar2(100), gender varchar2(100), num varchar2(100), tel varchar2(100), etl_date date );CREATE TABLE t_people_info2 ( id number(4), infotype varchar2(10),

2、info varchar2(25), etl_date date );INSERT INTO people_info VALUES(1001,gender,男);INSERT INTO people_info VALUES(1001,name,张三炮);INSERT INTO people_info VALUES(1001,num,01);INSERT INTO people_info VALUES(1001,tel;INSERT INTO people_info VALUES(1002,gender,女);INSERT INTO people_info VALUES

3、(1002,name,李思思);INSERT INTO people_info VALUES(1002,num,02);INSERT INTO people_info VALUES(1002,tel;（1）.创建表people_info源表CREATE TABLE people_info ( id number(4), infotype varchar2(10), info varchar2(25) );添加数据：INSERT INTO people_info VALUES(1001,gender,男);INSERT INTO people_info VALUES(1

4、001,name,张三炮);INSERT INTO people_info VALUES(1001,num,01);INSERT INTO people_info VALUES(1001,tel;INSERT INTO people_info VALUES(1002,gender,女);INSERT INTO people_info VALUES(1002,name,李思思);INSERT INTO people_info VALUES(1002,num,02);INSERT INTO people_info VALUES(1002,tel;

5、为了方便把表名改为ODS_PEOPLE_INFO（2）。我们想将如下格式转成：我们首先用SQL语句实现一下：select ID, max(case when INFOTYPE=name then INFO else null end) as name, max(case when INFOTYPE=gender then INFO else null end) as gender, max(case when INFOTYPE=num then INFO else null end) as num, max(case when INFOTYPE=tel then INFO else null

6、end) as telfrom ODS_PEOPLE_INFOgroup by ID（3）.在EDW层中创建源ODS_PEOPLE_INFO(4).通过源表生成目标表EDW_PEOPLE_INFO(5).把目标表EDW_PEOPLE_INFO生成到目标数据库中：(6).创建映射M_ORA_EDW_PEOPLE_INFO拖拽表达式：拖一个聚合汇总组件在拖拽一个FX表达式好用ETLDATECTRL+S保存（7）.创建任务S_ORA_EDW_PEOPLE_INFO然后双击任务：选择映射：(8).新建工作流W_ORA_EDW_PEOPLE_INFO保存，右击开启工作流(9).测试数据：+如下：想转成：注意：必是“”,单引无效select max(case when deptno=10 then empcount else 0 end) as 10, max(case when deptno=20 then empcount else 0 end) as 20, max(case when deptno=30 then