datastage中使用Pivot Enterprise stage实现行转列

怼烎@ 2022-04-16 00:42 209阅读 0赞

一、行转列  
通过datastage实现输入数据如下：

<table> 
 <thead> 
  <tr> 
   <th>REPID</th> 
   <th>last_name</th> 
   <th>Jan_sales</th> 
   <th>Feb_sales</th> 
   <th>Mar_sales</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>100</td> 
   <td>Smith</td> 
   <td>1234.08</td> 
   <td>1456.80</td> 
   <td>1578.00</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Yamada</td> 
   <td>1245.20</td> 
   <td>1765.00</td> 
   <td>1934.22</td> 
  </tr> 
 </tbody> 
</table>

输出数据如下：

<table> 
 <thead> 
  <tr> 
   <th>REPID</th> 
   <th>last_name</th> 
   <th>Q1sales</th> 
   <th>Pivot_index</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>100</td> 
   <td>Smith</td> 
   <td>1234.08</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>100</td> 
   <td>Smith</td> 
   <td>1456.80</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>100</td> 
   <td>Smith</td> 
   <td>1578.00</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Yamada</td> 
   <td>1245.20</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Yamada</td> 
   <td>1765.00</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>101</td> 
   <td>Yamada</td> 
   <td>1934.22</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

作业实现如下:  
(1)将测试数据写入测试文件  
![inputdata][]  
(2)通过Sequential File stage读取数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70]  
（3）选择Pivot Enterprise stage，Sequential File stage的输出作为Pivot Enterprise stage的输入，同时Pivot Enterprise stage输出到另一个Sequential File stage中  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1][]  
（4）在Pivot Enterprise stage stage–>Properties中选择Horizontal  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2][]  
（5）在Pivot Enterprise stage stage–>Pivot Properties配置要转换的列及为转换后的列添加一个索引号  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3]  
如上图所示  
①勾选Pivot Index，此时会自动出现Pivot\_index这一行  
②手工添加转换后的字段pivotted，并设置字段类型，长度，精度  
③双击pivotted行的Derivation处，或者单机下图标注的地方  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4]  
添加需要转换的字段，点确定即可  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 5][]  
（6）选择输出的字段  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 6]  
（7）运行程序，查看结果  
![view data][]

作业下载位置  
[https://download.csdn.net/download/bfhai/10785308][https_download.csdn.net_download_bfhai_10785308]

也可以直接联系我获取  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 7]

[inputdata]: /images/20220416/9660a5b47baf48b991e35b2b948656e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70]: /images/20220416/2bbec52947f643979cd596a6668358d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 1]: /images/20220416/f7b189c4f8d44d8aaa197d8715c262e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 2]: /images/20220416/b2a0f7253eef4f3683eda905e1abb2bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 3]: /images/20220416/1c5cddd8f9834381a33bfd9d796769df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 4]: /images/20220416/5fc5b021f8d9401a884723f9df12c375.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 5]: /images/20220416/cb29adf728c5408aa574492a240eae00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 6]: /images/20220416/3b15ba4c7c98438a9af86b5c190effca.png
[view data]: /images/20220416/642e18d7ad444eb38b22338d5e0a8f9d.png
[https_download.csdn.net_download_bfhai_10785308]: https://download.csdn.net/download/bfhai/10785308
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JmaGFp_size_16_color_FFFFFF_t_70 7]: /images/20220416/f86b246929e54841b24b572b6b635b0a.png