第一種：
通用的針對單表用的交叉表存儲過程
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
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 )
????
日期
:
*/
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 < 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
???? 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
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
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元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
