oracle或达梦--使用decode行转列、oracle行转列、case when行转列

目前很多行转列都使用了 oracle 11g新增的pivot ，但是我用达梦国产数据库不支持该函数，所以使用case when的方式

首先，我们需要构造一个两列的数据，也就是查询结果

select 'aaaaa' as flag ,'1' as num from dual union all
select 'bbbbb' as flag ,'2' as num from dual union all
select 'ccccc' as flag,'3' as num from dual union all
select 'ddddd' as flag,'4' as num from dual union all
select 'eeeee' as flag,'5' as num from dual 

查询结果如下

我们的目的是要flag里的每个行转成列，数量对应在每个列中。

select 
--max找出最大值,也可以用sum,目的是要整合成一行,去空
max(decode(flag,'aaaaa',num)) a, 
max(decode(flag,'bbbbb',num)) b, 
max(decode(flag,'ccccc',num)) c, 
max(decode(flag,'ddddd',num)) d, 
max(case when flag like '%e' then num WHEN flag is not NULL THEN 0 end) e
from (
select 'aaaaa' as flag ,'1' as num from dual union all
select 'bbbbb' as flag ,'2' as num from dual union all
select 'ccccc' as flag,'3' as num from dual union all
select 'ddddd' as flag,'4' as num from dual union all
select 'eeeee' as flag,'5' as num from dual  ) 

这样就能查询出我们想要的结果，decode需要我们准确的知道这个行的参数，case when能完成一些比较复杂的查询，比如模糊查询。

其原理就是把falg去匹配，如果匹配到参数至输出num，例如 aaaa和aaaaa比较，相等就输出num，记得要加max哦，能去空。

以下就是我们想要的结果