Creating Cross Tab Queries in SQL Server

SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  OFF  
GO

CREATE   PROCEDURE   [ dbo ] . [ SimpleXTab ]   @XField   varChar ( 20 ),  @XTable   varChar ( 20 ),
@XWhereString   varChar ( 250 ),  @XFunction   varChar ( 10 ),  @XFunctionField   varChar ( 20 ),  @XRow   varchar ( 40 )
  AS
Declare   @SqlStr   nvarchar ( 4000 )
Declare   @tempsql   nvarchar ( 4000 )
Declare   @SqlStrCur   nvarchar ( 4000 )
Declare   @col   nvarchar ( 100 )

set   @SqlStrCur   =  N ' Select [ '   +   @XField   +   ' ] into ##temptbl_Cursor from [ '   +   @XTable   +   ' ]   '   +   @XWhereString   +   '  Group By [ '   +   @XField   +   ' ] '

/**/ /* select @sqlstrcur */
exec  sp_executesql  @sqlstrcur



  declare  xcursor  Cursor    for    Select   *   from  ##temptbl_Cursor 

  open  xcursor 


  Fetch   next   from   xcursor 
  into   @Col  
 

While   @@Fetch_Status   =   0
Begin
   set   @Sqlstr   =   @Sqlstr   +  ", "
   set   @tempsql   =   isnull ( @sqlstr , '' )  +   isnull ( @XFunction   +   ' ( Case When  '   +   @XField   +  "  =   ' " +@Col +
                           " '   then   [ " + @XFunctionField + " ]   Else   0   End )  As   [ " + @XFunction + @Col + " ] " , '' )
   set   @Sqlstr   =   @tempsql
   Fetch   next   from  xcursor  into   @Col
 
End


  /**/ /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */

  set   @tempsql   =   ' Select  '    +   @XRow   +   ' ,  '   +   @Sqlstr   +   '  From  '   +   @XTable   +
                              @XWhereString   +    '  Group by  '   +   @XRow
  set   @Sqlstr   =   @tempsql

  Close  xcursor
  Deallocate  xcursor  

   set   @tempsql   =  N ' Drop Table ##temptbl_Cursor '
   exec  sp_executesql  @tempsql

  /**/ /*  Select @Sqlstr as [mk], len(@sqlstr) as [leng] */

    exec  sp_executesql  @Sqlstr
GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

以NorthWind举例说明使用情况：
先建个View：

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

CREATE   VIEW  dbo.vw_SampleQuery
AS
SELECT      dbo.Orders.ShipName, dbo.Categories.CategoryName, dbo.Orders.ShipCountry, (dbo. [ Order Details ] .UnitPrice  *  dbo. [ Order Details ] .Quantity) 
                       *  ( 1   -  dbo. [ Order Details ] .Discount)  AS  OrderAmt
FROM          dbo.Orders  INNER   JOIN
                      dbo. [ Order Details ]   ON  dbo.Orders.OrderID  =  dbo. [ Order Details ] .OrderID  INNER   JOIN
                      dbo.Products  ON  dbo. [ Order Details ] .ProductID  =  dbo.Products.ProductID  INNER   JOIN
                      dbo.Categories  ON  dbo.Products.CategoryID  =  dbo.Categories.CategoryID

GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

执行下面的语句：

Execute  SimpleXTab  ' CategoryName ' ,  ' vw_SampleQuery ' ,  '' ,  ' Sum ' ,  ' OrderAmt ' ,  ' ShipCountry '

效果图：


这些资料来自以下这个网址：
http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html