PIVOT初接触

以前一直没接触过PIVOT这个概念！当接触时，赶紧查了下资料如下：

其语法结构：

<pivot_clause> ::= ( 

       aggregate_function ( value_column )           

       FOR pivot_column           

       IN (<column_list>)

)  

<unpivot_clause> ::=( 

     value_column

     FOR pivot_column

     IN (<column_list>) 

) 

 

光看结构很难看懂，资料都是说 “为了实现行列转换”，感觉自己理解的怎么就不同了，我到学得它跟分组查询 差不多，

就像 select name,sum(score) group by name,只是在查询很复杂时可能才能够体现它的简洁明了吧！听高手说，PIVOT在执行时会转成你之前完全能用其它SQL语法写出来的SQL语句，我也不知道是不是真的，毕竟我也才接触PIVOT,网络上都是以下这个例子：

 

if object_id('test') is not null
drop table test
go

 

 

create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)

使用PIVOT将四个季度的利润转换成横向显示:

 

(1)方法一：
select id,name, [1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt

 

 

结果为：
id          name         一季度         二季度         三季度         四季度
----       --------        ------------ -----------      ---------      -----------
1           a                1000        2000        4000        10000
2           b                3000        3500        4200         5500

其实网上有种理解我到蛮认同的：

          它的计算过程是:先执行select id,name,sum(profile) from test group by id,name,quarter
          然后把相应的值填充到相应的位置，你用其它的聚合函数也对

即用分组查询出来的结果：select id,name,sum(profile) from test group by id,name,quarter

id          name                
-----      ---------         -----------
1           a                    1000
1           a                    2000
1           a                    4000
1           a                    5000
2           b                    3000
2           b                    3500
2           b                    4200
2           b                    5500

 

在从新换种布局显得跟明了，也就是把最后一列按条件变成了行的显示.

其实除了上面的方法以外还有其它的方法，有以下：

(2)方法二：

DECLARE @Str NVARCHAR(1000)
select @Str=isnull(@Str + ',','')+ '['+ cast(quarter as nvarchar(20)) + ']' from ( select distinct  quarter  from test) a
exec('select id,name,'+ @str +'from test '
+'PIVOT
(
 SUM(profile) for quarter IN ('+@str+')
)
as pvt')

(3)方法三：

第一种方法是直接把知道的情况变成列，那么如果我们不知道那种情况该设为哪列呢？如以A或B开头的是原料，以1,2,8开头的是成品呢？

这时就是这种情况啦？

select *
from
(
  select id,name,
  case when quarter=1 then '第一季度'
          when quarter=2 then '第二季度'
          when quarter=3 then '第三季度'
          when quarter=4 then '第四季度'
          else ''
  end as during,
  profile
  from test
  where quarter in ('1','2','3','4')
)p
PIVOT
(
  sum(profile)
  FOR
    during
  IN
    ([第一季度],[第二季度],[第三季度],[第四季度])
)
as pvt

这种情况到实际中会遇到的情况。

SELECT  date, sum([p1]) as p1, sum([p2]) as p2, sum([p3]) as p3

FROM

 (select date,

     case when substring(item,1,1) in ('A','B') then 'p1'

          when substring(item,1,1) in('D')  then  'p2'

          when substring(item,1,1) in( '5','6','7','8','9')  then 'p3'

          else ''

     end as type,

     qty

  from table_name

  where date between '2010-5-01' and '2010-9-01'

  and substring(item,1,1) in ('A','B', 'D', '5','6','7','8','9')

 ) p

PIVOT

(

 sum (qty)

 FOR type

   IN

 ([p1], [p2], [p3] )

) AS pvt

group by cutoffdate

 

不过这种情况需要求和的字段在p中已经有查询。

这是我初次接触PIVOT函数的理解.