oracle行转列之动态列

首先我们来看下oracle的行转列函数：

pivot 函数官方解释：https://www.oracle.com/cn/database/articles/technology/pivot-and-unpivot.html

首先我们创建相关表及数据

create table TEST_DATA
(
  goods_name   VARCHAR2(32),
  budget_date  VARCHAR2(32),
  budget_money NUMBER
);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('泡面', '2020-01', 20);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('泡面', '2020-02', 50);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('泡面', '2020-03', 60);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('火腿', '2020-01', 210);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('火腿', '2020-02', 530);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('火腿', '2020-03', 640);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('牛奶', '2020-01', 120);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('牛奶', '2020-02', 160);
insert into test_data (GOODS_NAME, BUDGET_DATE, BUDGET_MONEY)values ('牛奶', '2020-03', 140);

　我们的数据是这样的：

 

 

 

 我们需要的结果是：

名称　　2020-01　　2020-02　　2020-03　　.。。。

泡面　　20　　　　　　50　　　　60

火腿　　210　　　　　530　　　　640

牛奶　　120　　　　　650　　　　140

假如我们知道我们要转换的列为 2020-01 至2020-03 这时可以做如下转换：

select * from test_data 
pivot (sum(budget_money) for budget_date in('2020-01','2020-02','2020-03'));

　　

 

 

 但是当月份不确定时，上面这种写法就不适用了。我们查看官方文档后发现  pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出。这个时候我们将上面查询语句改造之后如下：

select * from test_data 
pivot xml (sum(budget_money) for budget_date in(select budget_date from  test_data));

　　

 

 该语句在不同版本的plsql中类型不同，在plsql14001961版本中显示xmltype类型。

这时查看budget_date_xml的值发现会将表中所有月份展示，结果如下

 

 当我高兴的认为我的问题解决了之后，我发现我草率了。

将sql放到mybatis中查询出来的结果没法办解析，网上找了一圈之后说是让你加一个xdb6.jar和xmlparserv2.jar,加完还是报错，最终找到可以在数据库层面将xmltype转换长varchar2

select goods_name,(BUDGET_DATE_xml).getstringval() from test_data 
pivot xml (sum(budget_money) for budget_date in(select budget_date from  test_data));

　　

 

 此时觉得已经ok了,但是屁股还没坐热就出现了新问题，varchar2最长4000,月份过多时会出现缓冲池内存不足的问题。这时心想可不可以转成colb这样就不会有问题了，抱着侥幸心理试了下getclobval，没想到还真有这方法！

select goods_name,(BUDGET_DATE_xml).getclobval() from test_data 
pivot xml (sum(budget_money) for budget_date in(select budget_date from  test_data));

　　

 

 至此，问题得到解决，如大神还有其他处理方法，欢迎在评论区留言，小弟愿闻其详。