sql查询

1.列转行

select ydlx name, nvl(sum(cntNum), 0) value  from mv_bjsc_yidiantj_day_gbyorgan unpivot(cntNum for ydlx in(

CNT_DOUBT_SFZD as

'无关紧要事件',

CNT_DOUBT_JSBS as

'重点事件',

CNT_DOUBT_WXSFZ as

'突发事件 '))

 where 1 = 1

 group by ydlx order by value desc

2.横向拼接查询结果

                     select a.yeartime,coalesce(a.acount,0) as aj ,coalesce(b.bcount,0) as zs from  (select  date_part('year', to_date(fasj,'yyyy-mm-dd')) yeartime ,count(*) as acount from  jxz_st_xsaj t1 where fasj>='2008-01-01' GROUP BY yeartime order by yeartime )a full join

                             (select  date_part('year', to_date(zssj,'yyyy-mm-dd')) yeartime ,count(*) as bcount from  jxz_st_zsryb t1 where zssj>='2008-01-01' GROUP BY yeartime     order by yeartime ) b on a.yeartime=b.yeartime;

select  date_part('year', to_date(fasj,'yyyy-mm-dd')) yeartime ,count(*) as acount from  jxz_st_xsaj t1 where fasj>='2008-01-01' GROUP BY yeartime   order by yeartime;

select  date_part('year', to_date(zssj,'yyyy-mm-dd')) yeartime ,count(*) as bcount from  jxz_st_zsryb t1 where zssj>='2008-01-01' GROUP BY yeartime  order by yeartime;