简单行专列

SQL语句： 

select a.TXZLB as ID, a.user_name,
    (case a.TXZLB when 1 then ‘特通通行证‘
    when 2  then ‘甲通通行证‘
    when 3  then ‘乙通通行证‘
    when 4  then ‘搬家通行证‘
    when 5  then ‘菜篮子通行证‘
    when 6  then ‘接送车停靠证‘
    when 7  then ‘三超通行证‘
    when 8  then ‘准行证‘
    else ‘临时通行证‘ end) typepmt,sum(case when caltype=‘add‘then pmtadd else 0 end  ) as totaladd,
                   sum(case when caltype=‘update‘then pmtadd else 0 end  ) as totalupdate,
                   sum(case when caltype=‘del‘then pmtadd else 0 end  ) as totaldel
    
     from
    (
       select ‘add‘ as caltype,count(TXZLB) as pmtadd,TXZLB, u.user_name  from t_traffic_permit  left join t_user u on u.n_user_id=t_traffic_permit.jbr where
      
        u.user_name = ‘admin‘ and 
        
     
      TJSJ between to_timestamp(‘2015-06-16‘,‘yyyy-mm-dd hh24:mi:ss‘) and to_timestamp(‘2017-07-17‘,‘yyyy-mm-dd hh24:mi:ss‘)  group by TXZLB, u.user_name
      
      union 
      
      select ‘update‘ as caltype, count(*) as pmtupdate,TXZLB,u.user_name from t_traffic_permit left join t_user u on u.n_user_id=t_traffic_permit.jbr where
     
        u.user_name = ‘admin‘ and 
   
        XGSJ between to_timestamp(‘2015-06-16‘,‘yyyy-mm-dd hh24:mi:ss‘) and to_timestamp(‘2017-07-17‘,‘yyyy-mm-dd hh24:mi:ss‘) and XGSJ is not null  group by u.user_name,TXZLB
     
     union
     
     select ‘del‘ as caltype, count(*) as pmtdel,TXZLB,u.user_name from t_traffic_permit left join t_user u on u.n_user_id=t_traffic_permit.jbr where 
    
        u.user_name = ‘admin‘ and 
    
      SCSJ between to_timestamp(‘2015-06-16‘,‘yyyy-mm-dd hh24:mi:ss‘) and to_timestamp(‘2017-07-17‘,‘yyyy-mm-dd hh24:mi:ss‘) and SCZT=1   group by u.user_name,TXZLB
    
     
     )a group by  a.TXZLB, a.user_name 

核心：

case when caltype=‘add‘ then pmtadd else 0 end （行转列语句）
union  （不同的结果连接到一起，但是查询字段的类型要一致）