Sql Server 经典：行列转换与透视(Pivot方法)

Sql server常用的分组聚合应用很广泛，但有时在展示时我们需要用到类似excel透视的效果；

那么如何达到这种透视效果呢？

如下例：

以Oracle经典库scott为例：

　　我们要求展现每个部门下(deptno)、各职(job)的所有综合工资是多少，正常情况下我们会使用如下查询

select deptno,job,SUM(sal) as sum_sal
    from emp
    group by deptno,job

效果如下：

 

但是如果我们要求deptno纵向排列，Job横向排列应该如何呢？

常规的写法如下：

--常规透视
select deptno,
    sum(case  when job=\'ANALYST\' then sal else 0 end) as ANALYST,
    sum(case  when job=\'CLERK\' then sal else 0 end) as CLERK,
    sum(case  when job=\'MANAGER\' then sal else 0  end) as MANAGER,
    sum(case  when job=\'PRESIDENT\' then sal  else 0 end) as PRESIDENT,
    sum(case  when job=\'SALESMAN\' then sal else 0  end) as SALESMAN
    from emp 
    group by deptno

 

得到的结果如下：

 

习惯表格操作的或许会感觉这样排列更加直观一点；

当然这是常规的写法，Sql Server 自带的Pivot方法也可以实现：不过对版本的要求至少是2008

如下：

--pivot透视    
    select deptno,ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN
        from
            (select deptno,job,sal from emp)as a
        pivot (sum(sal) 
            for job 
                in (ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN)) as b

效果同上

 

 

但是需要注意一点，如果这个查询的行和列相反(即：deptno横向，job纵向)，

因为deptno值全是数值，那么在书写的时候要注意一下，加上"[]"，

如下

select job,[10],[20],[30]
    from 
        (select job,deptno,sal from emp) a
    pivot (sum(sal) 
        for deptno 
            in ([10],[20],[30]) )b

效果如下：

 

 

这个是透视，逆透视--未完待续…………