`

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

 
阅读更多
<iframe align="top" marginwidth="0" marginheight="0" src="http://www.zealware.com/46860.html" frameborder="0" width="468" scrolling="no" height="60"></iframe>

第一种:

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

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></count>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 Shepker1999

Use pubs

GO

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]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/*

功能:交叉制表

参数 描述

@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)

日期:<chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="5" year="2005"></chsdate>5-1-2005

*/

CREATE PROCEDURE prCrosstab

@chrRowHead char(30),

@chrColHead char(30),

@chrValue char(30),

@chrSource char(30),

@inyType tinyint=1,

@inyGrouping tinyint=0

AS

/* 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 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

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

BEGIN

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

RETURN -1

END

/* Hold thedata 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 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

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

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

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

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>-----交叉表应用实例

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
<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>
<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>
<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>第三种:
<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>动态交叉表的实现
<iframe align="right" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog02.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>总体思路其实很简单,首先检索列头信息,形成一个游标,然后遍历游标,将上面查询语句里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 @strsql ='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_status0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN '' + @strTmpCol + '' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾

EXECUTE(@strsql) --执行



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


分享到:
评论

相关推荐

    基于SpringBoot的文件上传系统,前后端分离,单文件上传,多文件上传,大文件上传,断点续传,文件秒传,图片上传

    采用前后端分离的方式进行开发,实现了几种常用的文件上传功能。 前端采用 vue.js + plupload + element-ui 实现了文件在浏览器端的发送, 后端采用 spring boot + spring + spring mvc + mybatis 实现了文件在服务器...

    数据中台之结构化大数据存储设计.doc

    之后会深入剖析数据系统中 结构化数据的存储技术,介绍阿里云Tablestore选择哪种设计理念来更好的满足数据系统 中对结构化数据存储的需求。 2. 数据系统架构 1. 核心组件 上图是一个比较典型的技术架构,包含应用系统...

    基于nightmare的交互式爬虫.zip

    爬虫(Web Crawler)是一种自动化程序,用于从互联网上收集信息。其主要功能是访问网页、提取数据并存储,以便后续分析或展示。爬虫通常由搜索引擎、数据挖掘工具、监测系统等应用于网络数据抓取的场景。 爬虫的...

    微信小程序登录和后端SpringBoot交互操作.zip

    常用的存储形式包括关系型数据库、NoSQL数据库、JSON文件等。 遵守规则: 为避免对网站造成过大负担或触发反爬虫机制,爬虫需要遵守网站的robots.txt协议,限制访问频率和深度,并模拟人类访问行为,如设置User-...

    电子地图的设计与实现研究.doc

    本文主要研究一般电子地图的组成,结构,设计与实现方法,总结电子地图的优 势所在。 关键词:电子地图;设计;实现 0 前言 随着计算机技术及互联网技术的发展, 一种新的地图产品——网络电子地图悄然出现, 它正改变...

    java 面试题 总结

    异常表示程序运行过程中可能出现的非正常状态,运行时异常表示虚拟机的通常操作中可能遇到的异常,是一种常见运行错误。java编译器要求方法必须声明抛出可能发生的非运行时异常,但是并不要求必须声明抛出未被捕获的...

    php网络开发完全手册

    1.5 几种综合网络服务器系统的安装 14 1.5.1 XAMPP 14 1.5.2 WAMP 16 1.5.3 Appserv 17 1.5.4 EasyPHP 18 1.5.5 VertrigoServ 19 1.6 几种开发工具的介绍 20 1.6.1 Vi及Vim 20 1.6.2 Eclipse+PHPEclipse插件 21 ...

    Oracle.11g.从入门到精通 (1/2)

    10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询与删除 ...

    Oracle.11g.从入门到精通 (2/2)

    10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询与删除 ...

    亮剑.NET深入体验与实战精要2

    1.3.10 几个常用的数学函数 27 1.4 .NET的面向对象之门 27 1.4.1 继承——“子承父业” 28 1.4.2 委托——“任务书” 35 1.4.3 事件——“年终分红” 42 1.4.4 反射——“解剖” 49 1.5 .NET开发几把小刀 52 1.5.1 ...

    亮剑.NET深入体验与实战精要3

    1.3.10 几个常用的数学函数 27 1.4 .NET的面向对象之门 27 1.4.1 继承——“子承父业” 28 1.4.2 委托——“任务书” 35 1.4.3 事件——“年终分红” 42 1.4.4 反射——“解剖” 49 1.5 .NET开发几把小刀 52 1.5.1 ...

    超级有影响力霸气的Java面试题大全文档

     异常表示程序运行过程中可能出现的非正常状态,运行时异常表示虚拟机的通常操作中可能遇到的异常,是一种常见运行错误。java编译器要求方法必须声明抛出可能发生的非运行时异常,但是并不要求必须声明抛出未被捕获...

    net学习笔记及其他代码应用

    10.求以下表达式的值,写出您想到的一种或几种实现方法: 1-2+3-4+……+m [Page] 答: int Num = this.TextBox1.Text.ToString() ; int Sum = 0 ; for (int i = 0 ; i ; i++) { if((i%2) == 1) { Sum += i ; ...

    Oracle11g从入门到精通

    10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询...

    Oracle11g从入门到精通2

    10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件...

    工程硕士学位论文 基于Android+HTML5的移动Web项目高效开发探究

    1.3.2跨域交互即缓存处理方法 7 1.3.3页面自适应机制,即设备自适应与浏览器自适应机制 8 1.3.4 “认我测”质检服务平台的设计和实现 8 1.4 本文的结构安排 8 第二章 多窗口类浏览器设计 11 2.1 多窗口类浏览器需求...

    java面试常见基础(深层次,高级研发)

    14.4. 四、存储实现:put(key,vlaue) 38 14.5. 五、读取实现:get(key) 41 15. 一百万数据放Arraylist数组,怎么放? 在哪个代? 42 15.1.1. 调整数组容量 42 16. Hashmap和 concurrentHashmap除了线程安全 还有什么...

    PHP XML操作的各种方法解析(比较详细)

    本章将要介绍 PHP与 XML的操作方法,并对几个常用的 XML类库做一些简要介绍。 1 XML简介 XML是“可扩展性标识语言(eXtensible Markup Language)”的缩写,是一种类似于 HTML的标记性语言。但是与 H

    ado[1].net中文手册 学习 ado.net的重要资料

    输入和输出参数及返回值:描述如何将 DataSet 表中列的内容映射到 DataAdapter 命令属性的输入和输出参数(包括如何从存储过程中检索返回值)。 自动生成的命令:描述如何使用 CommandBuilder 为具有单个表 SELECT ...

    HTML 5实战

    在写作方式上,本书以一种开创性的方式使理论与实践达到极好的平衡,不仅对理论知识进行了清晰而透彻的阐述,而且根据读者理解这些知识的需要,精心设计了106个完整(每个案例分为功能描述、实现代码、效果展示和...

Global site tag (gtag.js) - Google Analytics