一个通用的Sql Server Stored Procedure(存储过程)用于显示Cross Tab Table,可以方便用于Cross Tab 报表,在access中也可以使用
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
![](/Images/OutliningIndicators/None.gif)
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)
![](/Images/OutliningIndicators/None.gif)
set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* select @sqlstrcur */
exec sp_executesql @sqlstrcur
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
declare xcursor Cursor for Select * from ##temptbl_Cursor
![](/Images/OutliningIndicators/None.gif)
open xcursor
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
Fetch next from xcursor
into @Col
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */
![](/Images/OutliningIndicators/None.gif)
set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + ' From ' + @XTable +
@XWhereString + ' Group by ' + @XRow
set @Sqlstr = @tempsql
![](/Images/OutliningIndicators/None.gif)
Close xcursor
Deallocate xcursor
![](/Images/OutliningIndicators/None.gif)
set @tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @tempsql
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
![](/Images/OutliningIndicators/None.gif)
执行下面的语句:
Execute SimpleXTab 'CategoryName', 'vw_SampleQuery', '', 'Sum', 'OrderAmt', 'ShipCountry'效果图:
![](http://www.oreillynet.com/network/2004/12/17/graphics/figure2.jpg)
这些资料来自以下这个网址:
http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html
posted on
2005-11-15 17:52
RubyPDF
阅读(
575)
评论()
编辑
收藏
举报