db2 行转列sql

行转列sql

select  max(case when char(crdate)='2016-06' then amt end )as amt1,max(case when char(crdate)='2016-07' then amt end )as amt1,
     max(  case when char(crdate)='2016-08' then amt end) as amt2,flag
from (
select VALUE(sum(amt),0) amt,SUBSTR(char(t1.CRTDAT),1,7) as CRDATE ,t1.FLAG from t_order t1 where t1.FLAG='1' and SUBSTR(char(t1.CRTDAT),1,7)  between '2016-05' and '2016-10' group by SUBSTR(char(t1.CRTDAT),1,7) ,t1.flag
union all 
select VALUE(sum(amt),0) amt,SUBSTR(char(t2.CRTDAT),1,7) as CRDATE,t2.FLAG from t_order t2 where t2.FLAG='2' and SUBSTR(char(t2.CRTDAT),1,7) between '2016-05' and '2016-10'  group by SUBSTR(char(t2.CRTDAT),1,7) ,t2.flag
) group by flag