DECLARE abc CURSOR
9 /* select DISTINCT Years from Try */
10 FOR @bbb--8,9,10改为这样就行了.exec('DECLARE abc CURSOR FOR '+ @bbb)
9 /* select DISTINCT Years from Try */
10 FOR @bbb--8,9,10改为这样就行了.exec('DECLARE abc CURSOR FOR '+ @bbb)
邹建你好：
我写的 Sql Server 通用交叉表存储过程，请指教。
1. 存储过程代码
CREATE PROCEDURE MyCrossTable (@FirstColumn varchar(30) ,@NewColumn varchar(30) , @SumColumn varchar(30),@Other varchar(100) ,@TableName varchar(30),@Empty varchar(4) = ' 0') as DECLARE @SelectSring varchar(8000)
DECLARE @ColumnName varchar(8000)
DECLARE @Datas varchar(100)
DECLARE @DistinctStr varchar(100)
set @DistinctStr = ' select DISTINCT ' + @NewColumn + ' FROM ' + @TableName
set @ColumnName = ''
EXEC ('DECLARE abc CURSOR For ' + @DistinctStr)
OPEN abc
FETCH NEXT FROM abc into @Datas
while @@fetch_status = 0
begin
set @ColumnName = @ColumnName + ",sum(case "+ @NewColumn + " when '" + @Datas + "' then " + @SumColumn + " else " + @Empty + " end) as '" + @Datas + "'"
fetch next from abc into @Datas
end
close abc
deallocate abc
set @SelectSring = 'select ' + @FirstColumn + ' ' + @ColumnName + ' ' + @Other
exec (@SelectSring)
GO
2. 参数说明
1）@FirstColumn varchar(30)
指定为行标头的列名
2) @NewColumn varchar(30)
指定为列标头的列名
3) @SumColumn varchar(30)
被计算的列的列名，此列必须为数字类型字段
4) @TableName varchar(30)
表名
5)@Other varchar(100)
结尾部分的 Select 语句
6) @Empty varchar(4)
在null 值时如何被填充，默认为 0.
此参数只能为 null 或 0
3. 执行
1)在查询分析器里运行
--删表
--在查询分析器里运行：
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Try')
BEGIN
PRINT 'Dropping Table Try'
DROP TABLE Try
END
DROP TABLE #TempTable
GO
--建表：
--在查询分析器里运行：
CREATE TABLE [Try] (
[NewName] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Years] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Counts] [int] NULL
)
GO
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'A',N'2001',1)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'B',N'2001',1)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'C',N'2002',1)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'D',N'2003',1)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'E',N'2004',1)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'A',N'2001',2)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'A',N'2000',3)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'B',N'2001',5)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'C',N'2000',2)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'D',N'2003',6)
INSERT INTO [try] ([NewName],[Years],[Counts]) values (N'E',N'2004',1)
Go
2)在Sql Server 中运行
将存储过程代码复制到 Sql Server 中
4. 例表数据
NewName Years Counts
A 2001 1
B 2001 1
C 2002 1
D 2003 1
E 2004 1
A 2001 2
A 2000 3
B 2001 5
C 2000 2
D 2003 6
E 2004 1
4. 执行结果
(在查询分析器里运行)
1) MyCrossTable "newname as '类别\年份'" ,'years','counts',
"from try group by newname",'try'
类别\年份 2000 2001 2002 2003 2004
A 3 3 0 0 0
B 0 6 0 0 0
C 2 0 1 0 0
D 0 0 0 7 0
E 0 0 0 0 2
2) MyCrossTable "newname as '类别\年份'" ,'years','counts',
",sum(counts) as ' 合 计 ' from try group by newname",'try'
类别\年份 2000 2001 2002 2003 2004 合 计
A 3 3 0 0 0 6
B 0 6 0 0 0 6
C 2 0 1 0 0 3
D 0 0 0 7 0 7
E 0 0 0 0 2 2
3) MyCrossTable "newname as '类别\年份'" ,'years','counts',
",Count(counts) as ' 计 数 ' from try group by newname",'try'
类别\年份 2000 2001 2002 2003 2004 计 数
A 3 3 0 0 0 3
B 0 6 0 0 0 2
C 2 0 1 0 0 2
D 0 0 0 7 0 2
E 0 0 0 0 2 2
4) MyCrossTable "newname as '类别\年份'" ,'years','counts',
",Count(counts) as ' 计 数 ' from try group by newname",'try'
类别\年份 2000 2001 2002 2003 2004 合 计
A 3 3 0 0 0 6
B 0 6 0 0 0 6
C 2 0 1 0 0 3
D 0 0 0 7 0 7
E 0 0 0 0 2 2
5) MyCrossTable "newname as '类别\年份'" ,'years','counts',
",Count(counts) as ' 计 数 ' from try group by newname",'try','null'
(设置第六个参数,此参数只能为 null 或 0,默认为 0)
类别\年份 2000 2001 2002 2003 2004 合 计
A 3 3 NULL NULL NULL 6
B 0 6 NULL NULL NULL 6
C 2 NULL 1 NULL NULL 3
D NULL NULL NULL 7 NULL 7
E NULL NULL NULL NULL 2 2