常用的几种交互表存储过程的实现方法

第一种：

通用的针对单表用的交叉表存储过程

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[p_qry]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [dbo].[p_qry]
GO

/*--生成交叉表的简单通用存储过程

           根据指定的表名,纵横字段,统计字段,自动生成交叉表
           并可根据需要生成纵横两个方向的合计

           注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
           如果不要此功能,则去掉交换处理部分

--邹建 204.06--*/

/*--调用示例

           exec p_qry ''syscolumns'',''id'',''colid'',''colid'',1,1
--*/

create proc p_qry
@TableName sysname,            --表名
@纵轴 sysname,                        --交叉表最左面的列
@横轴 sysname,                        --交叉表最上面的列
@表体内容 sysname,            --交叉表的数数据字段
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit            --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)

--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s=''declare @a sysname
if(select case when count(distinct [''+@纵轴+''])<count(distinct [''+@横轴+'']) then 1 else 0 end
from [''+@TableName+''])=1
           select @a=@纵轴,@纵轴=@横轴,@横轴=@a''
exec sp_executesql @s
           ,N''@纵轴 sysname out,@横轴 sysname out''
           ,@纵轴 out,@横轴 out

--生成交叉表处理语句
set @s=''
set @s=''''''''
select @s=@s+'''',[''''+cast([''+@横轴+''] as varchar)+'''']=sum(case [''+@横轴
           +''] when ''''''''''''+cast([''+@横轴+''] as varchar)+'''''''''''' then [''+@表体内容+''] else 0 end)''''
from [''+@TableName+'']
group by [''+@横轴+'']''
exec sp_executesql @s
           ,N''@s varchar(8000) out''
           ,@sql out

--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
                       when 1 then '',[合计]=sum([''+@表体内容+''])''
                       else '''' end
           ,@sum2=case @是否家纵向合计
                       when 1 then ''[''+@纵轴+'']=case grouping([''
                                   +@纵轴+'']) when 1 then ''''合计'''' else cast([''
                                   +@纵轴+''] as varchar) end''
                       else ''[''+@纵轴+'']'' end
           ,@sum3=case @是否家纵向合计
                       when 1 then '' with rollup''
                       else '''' end

--生成交叉表
exec(''select ''+@sum2+@sql+@sum1+''
from [''+@TableName+'']
group by [''+@纵轴+'']''+@sum3)
go

第二种：

一个通用的交叉制表存储过程

原作者 :John Papa, Matthew Shepker 1999

Use pubs

exec prCrosstab 'stor_id','ord_date','qty','sales',1,5

结果：

RowHead	Year_1992	Year_1993	Year_1994
6380	0	0	8
7066	0	50	75
7067	80	0	10
7131	0	85	45
7896	0	60	0
8042	0	55	25

以下为生成脚本：

if exists ( select * from dbo.sysobjects where id = object_id (N'[dbo].[prCrosstab]') and OBJECTPROPERTY (id, N'IsProcedure') = 1)

drop procedure [dbo].[prCrosstab]

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

功能:交叉制表

参数描述

@chrRowHead 表示列,在交叉制表的结果中作为第一出现

@chrColHead 表示列,在交叉制表的结果中该列中的数据被变换为新列名称

@chrValue 表示列,在该列中执行聚合函数

@chrSource 源表或视图

@inyType 1- 求和,2-平均值,3-最小值,4-最大值,5-计数

@inyGrouping 1- 工作日,2-年内的周数,3-月份,4-季度,5-年份

Author:John Papa Matthew Shepker

整理: 水如烟( http://blog.csdn.net/lzmtw )

日期 : 5-1-2005

CREATE PROCEDURE prCrosstab

@chrRowHead char (30),

@chrColHead char (30),

@chrValue char (30),

@chrSource char (30),

@inyType tinyint =1,

@inyGrouping tinyint =0

/* Variables for the procedure */

DECLARE

@chvRow varchar (255),

@chvCol varchar (255),

@chvVal varchar (255),

@chvType varchar (10),

@chvRowType varchar (10),

@chvColType varchar (255),

@chvTemp varchar (255),

@chvColTemp varchar (255),

@chvRowTemp varchar (255),

@intType int ,

@intRowType int ,

@intColType int ,

@chvExec varchar (255),

@chvGroup varchar (255),

@fltTemp float ,

@dtmTemp datetime ,

@insR smallint ,

@intColumn int ,

@intReturn int ,

@intTemp int ,

@intColNameLen int ,

@intMaxRowHead int

SET NOCOUNT ON

/* Check if source exists */

IF NOT EXISTS

( SELECT *

FROM sysobjects

WHERE name = @chrSource

AND type IN ('v','u'))

BEGIN

RAISERROR 51001 'Source does not exists.'

RETURN -1

END

/* Check for column existence */

IF NOT EXISTS

( SELECT sc.name

FROM syscolumns sc

JOIN sysobjects so ON sc.id = so.id

WHERE so.name = @chrSource

AND sc.name = @chrColHead)

BEGIN

RAISERROR 51002 'Invalid @chrColHead name.'

RETURN -1

END

IF NOT EXISTS

( SELECT sc.name

FROM syscolumns sc

JOIN sysobjects so ON sc.id = so.id

WHERE so.name = @chrSource

AND sc.name = @chrRowHead)

BEGIN

RAISERROR 51002 'Invalid @chrRowHead name.'

RETURN -1

END

IF NOT EXISTS

( SELECT sc.name

FROM syscolumns sc

JOIN sysobjects so ON sc.id = so.id

WHERE so.name = @chrSource

AND sc.name = @chrValue)

BEGIN

RAISERROR 51002 'Invalid @chrValue name.'

RETURN -1

END

/* Verify type is valid (1(sum),2(avg),etc...) */

IF @inyType < 1 OR @inyType > 5

BEGIN

RAISERROR 51000 'Invalid crosstab type.'

RETURN -1

END

/* Create typestr to hold aggregate name */

SELECT @chvType=

CASE @inyType

WHEN 1 THEN 'SUM'

WHEN 2 THEN 'AVG'

WHEN 3 THEN 'MAX'

WHEN 4 THEN 'MIN'

WHEN 5 THEN 'COUNT'

ELSE 'SUM'

END

/* Getstandard data type of @chrValue column */

SELECT @chvTemp = t2.name

FROM sysobjects o

JOIN syscolumns c ON o.id = c.id

JOIN systypes t1 ON t1.usertype = c.usertype

JOIN systypes t2 ON t1.type = t2.type

WHERE t2.usertype < 100

AND t2.usertype <> 18

AND t2.usertype <> 80

AND o.type IN ('u','v')

AND o.name = @chrSource

AND c.name = @chrValue

/* Categorize types for aggregate check */

SELECT @intTemp =

CASE

WHEN @chvTemp IN ('int','smallint','tinyint','float','real',

'decimal','numeric','monery','smallmoney') THEN 1

WHEN @chvTemp IN ('datetime','smalldatetime') THEN 3

WHEN @chvTemp IN ('bit','char','varchar') THEN 5

ELSE 100

END

/* validate existing data type is consistent with selected aggregate */

IF @inyType < @intTemp

BEGIN

RAISERROR 51020 'Crosstab type not valid with @chrValue definition.'

RETURN -1

END

/* Hold the data type for future use */

SELECT @chvColType = RTRIM (

CASE @inyType

WHEN 5 THEN 'int'

ELSE CASE

WHEN @chvTemp IN ('bit','char','varchar') THEN 'int'

WHEN @chvTemp IN ('decimal','numeric') THEN 'float'

ELSE @chvTemp

END

END )

/* Verify grouping is valid for colhead */

IF @inyGrouping < 0 OR @inyGrouping > 5

BEGIN

RAISERROR 51010 'Invalid crosstab grouping.'

RETURN -1

END

/* Getstandard data type of @chrColHead column */

SELECT @chvTemp = t2.name

FROM sysobjects o

JOIN syscolumns c ON o.id = c.id

JOIN systypes t1 ON t1.usertype = c.usertype

JOIN systypes t2 ON t1.type = t2.type

WHERE t2.usertype < 100

AND t2.usertype <> 18

AND t2.usertype <> 80

AND o.type IN ('u','v')

AND o.name = @chrSource

AND c.name = @chrColHead

IF UPPER (@chvTemp) NOT IN ('CHAR','VARCHAR')

SELECT @intColtype = 1

ELSE

SELECT @intColtype = 0

/* Get standard data type of @chvRowHead */

SELECT @chvRowType = t2.name

FROM sysobjects o

JOIN syscolumns c ON o.id = c.id

JOIN systypes t1 ON t1.usertype = c.usertype

JOIN systypes t2 ON t1.type = t2.type

WHERE t2.usertype < 100

AND t2.usertype <> 18

AND t2.usertype <> 80

AND o.type IN ('u','v')

AND o.name = @chrSource

AND c.name = @chrRowHead

IF UPPER (@chvRowType) NOT IN ('CHAR','VARCHAR')

SELECT @intRowtype = 1

ELSE

SELECT @intRowtype = 0

/* Categorize types for grouping check */

SELECT @intTemp =

CASE

WHEN @chvTemp IN ('int','smallint','tinyint','float','real',

'decimal','numeric','monery','smallmoney') THEN 1

WHEN @chvTemp IN ('datetime','smalldatetime') THEN 3

WHEN @chvTemp IN ('bit','char','varchar') THEN 5

ELSE 100

END

/* Validate existing data type is consistant with selected grouping */

IF (@intTemp = 5 AND @inyGrouping > 0) OR (@intTemp = 1 AND @inyGrouping > 0)

OR (@intTemp =3 AND @inyGrouping =0)

BEGIN

RAISERROR 51030 'Crosstab grouping not valid with @chvColHead definition.'

RETURN -1

END

/* Check for permission on source */

IF user_id () <> 1

BEGIN

IF ( SELECT COUNT ( DISTINCT c.name)

FROM syscolumns c,sysobjects o,sysprotects p,

sysusers u,master..spt_values v

WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)

AND c.id = o.id

AND p.id = c.id

AND c.colid = v.number

AND v.type = 'p'

AND o.id = object_id (@chrSource)

AND (u.uid = user_id () OR u.uid IN

( SELECT u1.uid

FROM sysusers u1

WHERE u1.gid = u1.uid

AND u1.gid IN

( SELECT u2.gid

FROM sysusers u2

WHERE u2.uid = user_id ()

OR u2.uid = user_id ('public'))))

AND p.uid = u.uid

AND p.action = 193

AND p.protecttype = 205

AND columns IS NOT NULL

AND CASE SUBSTRING (p.columns,1,1) & 1

WHEN null THEN 255

WHEN 0 THEN CONVERT ( tinyint , SUBSTRING (p.columns,v.low,1))

ELSE ( CONVERT ( tinyint , ISNULL ( SUBSTRING (p.columns,v.low,1),0)))

END & v.high <> 0

AND NOT EXISTS

( SELECT *

FROM syscolumns c5,sysobjects o5,

sysprotects p5,sysusers u5,

master..spt_values v5

WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)

AND c5.colid = c.colid

AND c5.id = c5.id

AND c5.colid = v5.number

AND v5.type = 'p'

AND o5.id = object_id (@chrSource)

AND (u5.uid = user_id () OR u5.uid IN

( SELECT u6.uid

FROM sysusers u6

WHERE u6.gid = u6.uid

AND u6.gid IN

( SELECT u7.gid

FROM sysusers u7

WHERE u7.uid = user_id ()

OR u7.uid = user_id ('public'))))

AND p5.uid = u5.uid

AND p5.action = 193

AND p5.protecttype = 206

AND p5.columns IS NOT NULL

AND CASE SUBSTRING (p5.columns,1,1) & 1

WHEN NULL THEN 255

WHEN 0 THEN CONVERT ( tinyint , SUBSTRING (p5.columns,v5.low,1))

ELSE ( CONVERT ( tinyint , ISNULL ( SUBSTRING (p5.columns,v5.low,1),0)))

END & v5.high <> 0)) <> 3

BEGIN

RAISERROR 51003 'Permission denied on column.'

RETURN -1

END

/* Create table #colnames and #rownames */

CREATE TABLE #colnames (colname varchar (255) NULL ,colnumber int NULL )

CREATE TABLE #rownames (rowname varchar (255) NULL )

/* Insert distinct column data into #colnames */

SELECT @chvExec = 'insert #colnames select col1,col2 from '

+ '(select distinct col1 = ' +

CASE @intTemp

WHEN 3 THEN

CASE

WHEN @inyGrouping IN (1,3) THEN 'datename(' +

CASE @inyGrouping

WHEN 1 THEN 'weekday'

WHEN 3 THEN 'month'

END + ',' + RTRIM (@chrColHead) + ')'

ELSE CASE @inyGrouping

WHEN 2 THEN '''Week'

WHEN 4 THEN '''Quarter'

WHEN 5 THEN '''Year'

END + '_'' + ' + 'datename(' +

CASE @inyGrouping

WHEN 2 THEN 'week'

WHEN 4 THEN 'quarter'

WHEN 5 THEN 'year'

END + ',' + RTRIM (@chrColHead)+ ')'

END

ELSE CASE @intColType

WHEN 1 THEN 'convert(varchar(255), ' + RTRIM (@chrColHead) + ')'

ELSE RTRIM (@chrColHead)

END

END + ',col2 = '+

CASE @intTemp

WHEN 3 THEN 'datepart(' +

CASE @inyGrouping

WHEN 1 THEN 'weekday'

WHEN 2 THEN 'week'

WHEN 3 THEN 'month'

WHEN 4 THEN 'quarter'

WHEN 5 THEN 'year'

END + ',' + RTRIM (@chrColHead) + ')'

ELSE '0'

END + ',col3 = '+

CASE @intTemp

WHEN 3 THEN 'datepart(' +

CASE @inyGrouping

WHEN 1 THEN 'weekday'

WHEN 3 THEN 'month'

WHEN 2 THEN 'week'

WHEN 4 THEN 'quarter'

WHEN 5 THEN 'year'

END + ',' + RTRIM (@chrColHead) + ')'

ELSE RTRIM (@chrColHead)

END + ' from ' + RTRIM (@chrSource) + ') xyz order by col3'

--PRINT @chvExec

EXEC (@chvExec)

/* Check column count */

IF ( SELECT COUNT (*) FROM #colnames) > 1023

BEGIN

DROP TABLE #colnames

RAISERROR 51004 'Distinct column count exceeded max of 1023.'

RETURN -1

END

/* Verify colnames do not exceed max length */

IF ( SELECT MAX ( DATALENGTH ( RTRIM (colname)) - 1) FROM #colnames) > 29

BEGIN

DROP TABLE #colnames

RAISERROR 51050 'Column data length exceeded max of 30.'

RETURN -1

END

/* If all is OK,continue to add #rownames data */

SELECT @chvExec = 'insert #rownames select distinct ' +

CASE @intRowType

WHEN 1 THEN 'convert(varchar(255), '

ELSE ''

END + RTRIM (@chrRowHead) +

CASE @intRowType

WHEN 1 THEN ')'

ELSE ''

END + ' from ' + @chrSource

--PRINT @chvExec

EXEC (@chvExec)

Would be nice if you could use this value to define the crosstable

but this table must be created in a non-dynamic fashion.

SELECT @intMaxRowHead=

( SELECT MAX ( DATALENGTH ( RTRIM (rowname))) FROM #rownames)

/* Create crosstable */

/* Define crosstable with rowhead field */

CREATE TABLE crosstable (rowhead varchar (255) NULL )

/* Alter crosstable by adding columns based on #colnames data */

DECLARE colname_cursor2 CURSOR FOR

SELECT colname FROM #colnames

OPEN colname_cursor2

FETCH colname_cursor2 INTO @chvCol

WHILE @@fetch_status >= 0

BEGIN

SELECT @chvColTemp = ''

IF @chvCol LIKE '%[^A-Z0-9]%'

BEGIN

SELECT @insR=1

WHILE @insR <= DATALENGTH ( RTRIM (@chvCol))

BEGIN

SELECT @chvColTemp = RTRIM (@chvColTemp) +

CASE

WHEN SUBSTRING (@chvCol,@insR,1) LIKE '[A-Z0-9_]'

THEN SUBSTRING (@chvCol,@insR,1)

ELSE ''

END

SELECT @insR = @insR + 1

END

SELECT @chvCol = @chvColTemp

END

SELECT @chvExec = 'alter table ' + user_name () + '.crosstable add '+

CASE

WHEN SUBSTRING (@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol

ELSE '_' + LTRIM (@chvCol)

END + ' ' + @chvColType + ' null default(0)'

--PRINT @chvExec

EXEC (@chvExec)

FETCH colname_cursor2 INTO @chvCol

END

CLOSE colname_cursor2

DEALLOCATE colname_cursor2

/* Add #rowhead data to crosstable */

SELECT @chvExec='insert ' + USER_NAME () + '.crosstable (rowhead) select rowname from #rownames'

--PRINT @chvExec

EXEC (@chvExec)

Create cursor with @chvRowHead and @chvColHead groupings and @chvValue

aggregate

SELECT @chvExec = 'declare colname_cursor3 cursor for select ' +

CASE @intRowType

WHEN 1 THEN 'convert(varchar(255),' + RTRIM (@chrRowHead) + ')'

ELSE RTRIM (@chrRowHead)

END + ',' +

CASE

WHEN @intTemp = 3 THEN

CASE

WHEN @inyGrouping IN (1,3) THEN 'datename(' +

CASE @inyGrouping

WHEN 1 THEN 'weekday'

WHEN 3 THEN 'month'

END + ',' + RTRIM (@chrColHead) + ')'

ELSE CASE @inyGrouping

WHEN 2 THEN '''Week'

WHEN 4 THEN '''Quarter'

WHEN 5 THEN '''Year'

END + '_'' + '+ 'datename(' +

CASE @inyGrouping

WHEN 2 THEN 'week'

WHEN 4 THEN 'quarter'

WHEN 5 THEN 'year'

END + ',' + RTRIM (@chrColHead) + ')'

END

ELSE CASE @intColType

WHEN 1 THEN 'convert(varchar(255),' + RTRIM (@chrColHead) + ')'

ELSE RTRIM (@chrColHead)

END

END + ',total = convert(varchar(255),' + RTRIM (@chvType) + '(' +

RTRIM (@chrValue) + ')) from ' + RTRIM (@chrSource) + ' group by ' +

RTRIM (@chrRowHead) + ',' +

CASE @intTemp

WHEN 3 THEN

CASE

WHEN @inyGrouping IN (1,3) THEN 'datename(' +

CASE @inyGrouping

WHEN 1 THEN 'weekday'

WHEN 3 THEN 'month'

END + ',' + RTRIM (@chrColHead) + ')'

ELSE CASE @inyGrouping

WHEN 2 THEN '''Week'

WHEN 4 THEN '''Quarter'

WHEN 5 THEN '''Year'

END + '_'' + ' + 'datename(' +

CASE @inyGrouping

WHEN 2 THEN 'week'

WHEN 4 THEN 'quarter'

WHEN 5 THEN 'year'

END + ',' + RTRIM (@chrColHead) + ')'

END

ELSE RTRIM (@chrColHead)

END

--PRINT @chvExec

EXEC (@chvExec)

/* Iterate through cursor and update crosstable */

BEGIN TRAN

OPEN colname_cursor3

FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal

WHILE @@fetch_status >= 0

BEGIN

SELECT @chvColTemp = ''

IF @chvCol LIKE '%[^A-Z0-9]%'

BEGIN

SELECT @insR=1

WHILE @insR <= DATALENGTH ( RTRIM (@chvCol))

BEGIN

SELECT @chvColTemp = RTRIM (@chvColTemp) +

CASE

WHEN SUBSTRING (@chvCol,@insR,1) LIKE '[A-Z0-9_]'

THEN SUBSTRING (@chvCol,@insR,1)

ELSE ''

END

SELECT @insR = @insR + 1

END

SELECT @chvCol = @chvColTemp

END

SELECT @chvExec = 'update ' + USER_NAME () + '.crosstable set ' +

CASE

WHEN SUBSTRING (@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol

ELSE '_' + LTRIM (@chvCol)

END + ' = ' +

CASE

WHEN @chvVal IS NULL THEN '0'

ELSE RTRIM (@chvVal)

END + ' where rowhead = '''

SELECT @chvRow =

CASE WHEN @chvRow IS NULL THEN 'NULL'

ELSE RTRIM (@chvRow)

END

SELECT @chvRowTemp = ''

IF @chvRow LIKE '%''%'

BEGIN

SELECT @insR = 1

WHILE @insR <= DATALENGTH ( RTRIM (@chvRow)) - 1

BEGIN

SELECT @chvRowTemp = RTRIM (@chvRowTemp) +

CASE

WHEN SUBSTRING (@chvRow,@insR,1) LIKE '[^'']' THEN

SUBSTRING (@chvRow,@insR,1)

ELSE ''''''

END

SELECT @insR = @insR + 1

END

SELECT @chvRow = @chvRowTemp

END

SELECT @chvExec = @chvExec + @chvRow + ''''

--PRINT @chvExec

EXEC (@chvExec)

FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal

END

CLOSE colname_cursor3

DEALLOCATE colname_cursor3

COMMIT TRAN

/* Send back the data from crosstable */

SET NOCOUNT OFF

SELECT @chvExec = 'select * from ' + USER_NAME () + '.crosstable'

--PRINT @chvExec

EXEC (@chvExec)

/* Drop the tables */

DROP TABLE #colnames

DROP TABLE #rownames

DROP TABLE crosstable

RETURN

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

-----交叉表应用实例

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)

Go

---以下为交叉表的列数是确定的

select name,sum(case subject when '数学' then source else 0 end) as '数学',

sum(case subject when '英语' then source else 0 end) as '英语',

sum(case subject when '语文' then source else 0 end) as '语文'

from test
group by name

---以下为交叉表的列数是不确定的

declare @sql varchar(8000)

set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject fro

第三种：

动态交叉表的实现

总体思路其实很简单，首先检索列头信息，形成一个游标，然后遍历游标，将上面查询语句里Case判断的内容用游标里的值替代，形成一条新的Sql查询，然后执行，返回结果，就可以了，以下是我写的一个存储过程，供大家参考：

CREATE procedure CorssTab
@strTabName as varchar(50) = 'Employees', --此处放表名
@strCol as varchar(50) = 'City',                       --表头分组依据字段
@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段
@strNumber as varchar(50) = 'ReportsTo',    --被统计的字段
@strSum as varchar(10) = 'Sum'                     --运算方式
AS

DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
SET nocount ON
SET @str sql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段

OPEN corss_cursor
while (0=0)
BEGIN
    FETCH NEXT FROM corss_cursor --遍历游标，将列头信息放入变量@strTmpCol
    INTO @strTmpCol
    if (@@fetch_status<>0) break
          SET @str sql = @str sql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN '' + @strTmpCol + '' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
        SET @str sql = @str sql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾

EXECUTE(@str sql ) --执行

IF @@error <>0 RETURN @@error --如果出错，返回错误代码
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 --释放游标，返回0表示成功

end
GO

几点说明：
a、这是一个通用存储过程，使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上，其中结果集的第二列我加了个合计列
b、为了测试方便，我在存储过程中设置了默认值，就是前面提到的Employees表，这样直接运行时就可以出来我上面提到的结果。
c、使用时，可以把上面的代码复制到企业管理器的查询设计界面Sql窗格，或者查询分析器里运行一下（注意正确选择NorthWind 数据库），就可以生成一个存储过程：CorssTab，然后直接运行CorssTab，如果出现本文前面类似的窗格，就表示运行成功了。
d、假如用于其它表，首先需要在你的用户数据库里生成此存储过程（当然也可以放到Master里，然后再加个变量：@DataBase，赋值为数据库名称，然后在上面代码打开指定数据库，这样所有的数据库都可以调用它），当你调用时，采取以下格式：

CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'

上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量，大家可以运行试一下效果，虽然列头显示的名称不恰当，但基本效果出来了，相信大家通过对我的代码再作简单修改，可以达到满意的交叉表效果。

示例 :

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1573678

常用的几种交互表存储过程的实现方法

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义