我们可以通过wm_concat函数可以实现行转列:
select s_info_windcode,rate from OptionEmbeddedBondRate where
s_info_windcode = '110031.SH'
目前库中的数据是如下图所示:
要转换成如下图所示的数据:
这时就可以通过wm_concat函数进行转换,具体sql如下:
select s_info_windcode,WM_CONCAT(rate) rate from OptionEmbeddedBondRate
WHERE s_info_windcode ='110031.SH'
GROUP BY s_info_windcode
上述结果集中会有一个问题,那就是在number类型转换成varchar类型时, .前面的0会被自动省略掉,为了解决这个问题,我们将参数rate做一下判断:
decode(substr((to_char(rate)),0,1),'.','0'||to_char(rate),to_char(rate))
最终的代码就如下所示:
select s_info_windcode,WM_CONCAT(decode(substr((to_char(rate)),0,1),'.','0'||to_char(rate),to_char(rate))) rate from OptionEmbeddedBondRate
WHERE s_info_windcode ='110031.SH'
GROUP BY s_info_windcode
下面则是最终聚集后的结果集:
当然,我们也可以为rate里面的数字集合做一个排序,当使用WM_CONCAT函数时,排序只能是先通过over(partition by … order by …)函数去做,然后再通过聚集函数max去取,具体的sql如下:
select s_info_windcode,MAX(to_char(M)) as RATE
from (select
s_info_windcode,
WM_CONCAT(decode(substr((to_char(rate)),0,1),'.','0'||to_char(rate),to_char(rate))) over(partition by s_info_windcode order by rate) m
from OptionEmbeddedBondRate
WHERE s_info_windcode ='110031.SH')
group by s_info_windcode;
具体的结果集如下: