SQL Server 2005 T-SQL学习笔记：建立动态的PIVOT

利用CTE，我们可以方便地一列中的所有值连接成一个字串，然后在PIVOT语句中使用，以下是示例：

 

-- --Static PIVOT===========
select   *   from  
(
     select   CustomerID,  datepart (yyyy,OrderDate)  as  Years,  1   As  Counts
     from     Sales.SalesOrderHeader
) piv PIVOT
(
     sum (Counts)
     for  Years  in  ( [ 2001 ] , [ 2002 ] , [ 2003 ] )
)  as  chd
Order   By  CustomerID

-- Dynamic PIVOT============
declare   @Years   as   nvarchar ( 100 );
with  YearCTE  as
(
     select   distinct   year (OrderDate)  as  YearNum  from  Sales.SalesOrderHeader
)
select   @Years    =   isnull ( @Years   +  N ' ,[ ' ,  ' [ '  )  +   cast (YearNum  as   nvarchar ( 4 ))  +   ' ] '     from  YearCTE  order   by  YearNum;

print   @Years ;

declare   @Sql   as   nvarchar ( max );

set   @Sql   =  N ' select * from 
(
    select  CustomerID, datepart(yyyy,OrderDate) as Years, 1 As Counts
    from    Sales.SalesOrderHeader
) piv PIVOT
(
    sum(Counts)
    for Years in ( '   +   @Years   +   ' )
) as chd
Order By CustomerID ' ;
execute  sp_executesql  @sql ;