按任意的字段旋转的存储过程
----------------------------------------------------------------------------
-- 分段截取函数
----------------------------------------------------------------------------
CREATE FUNCTION DBO.FUN_SplitStr(
@S VARCHAR(8000), -- 包含多个数据项的字符串
@POS INT, -- 要获取的数据项的位置
@SPLIT VARCHAR(10) -- 数据分隔符
) RETURNS VARCHAR(128)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN INT
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @POS>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0 BEGIN
SELECT @POS=@POS-1,
@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
END
RETURN LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1)
END
GO
-------------------------------------------
--行列转换
-------------------------------------------
-------------------------------------------
--行列转换
-------------------------------------------
CREATE PROC PRC_CORSSQUERY
@NVR_TABNAME AS NVARCHAR(1024) = '', -- 此处放表名
@NVR_XCOL AS NVARCHAR(1024) = '', -- 表头分组依据字段 (横向字段,以系统编号作为依据,内部转化,此字段只能有一个)
@NVR_YCOL AS NVARCHAR(1024) = '', -- 分组字段 (纵向字段,直接取名称列,可以有多个,多个用逗号分隔)
@NVR_STATCOL AS NVARCHAR(1024) = '', -- 被统计的字段 (值)
@NVR_WHERE AS NVARCHAR(4000)='', -- WHERE条件 (一定要带 WHERE 语句,如:WHERE 地区=''广州'' ) * 注意字符一定要用两个分号.
@NVR_TOTAL AS NVARCHAR(1024)='', -- 合计的公式( SUM,AVG ,将其放在第一列.如果多项,请用逗号分隔)
@NVR_ORDERBY AS NVARCHAR(1024)='' -- 排序字段,如: [海关编码],[地区]
AS BEGIN
--SET NOCOUNT ON
DECLARE @NVR_CMD AS NVARCHAR(4000)
DECLARE @NVR_XCOLNAME AS NVARCHAR(256)
DECLARE @NVR_SQL0 AS NVARCHAR(4000)
DECLARE @NVR_SQL1 AS NVARCHAR(4000)
DECLARE @NVR_SQL2 AS NVARCHAR(4000)
DECLARE @NVR_SQL3 AS NVARCHAR(4000)
DECLARE @NVR_SQL4 AS NVARCHAR(4000)
DECLARE @NVR_SQL5 AS NVARCHAR(4000)
DECLARE @NVR_SQL6 AS NVARCHAR(4000)
DECLARE @NVR_SQL7 AS NVARCHAR(4000)
DECLARE @NVR_SQL8 AS NVARCHAR(4000)
DECLARE @NVR_SQL9 AS NVARCHAR(4000)
DECLARE @NVR_SQL10 AS NVARCHAR(4000)
DECLARE @NVR_SQL11 AS NVARCHAR(4000)
DECLARE @NVR_SQL12 AS NVARCHAR(4000)
DECLARE @NVR_SQL13 AS NVARCHAR(4000)
DECLARE @NVR_SQL14 AS NVARCHAR(4000)
DECLARE @NVR_SQL15 AS NVARCHAR(4000)
DECLARE @NVR_SQL16 AS NVARCHAR(4000)
DECLARE @NVR_SQL17 AS NVARCHAR(4000)
DECLARE @NVR_SQL18 AS NVARCHAR(4000)
DECLARE @NVR_SQL19 AS NVARCHAR(4000)
DECLARE @NVR_SQL20 AS NVARCHAR(4000)
DECLARE @NVR_DATETYPE AS NVARCHAR(1)
DECLARE @INT_ID AS INT
DECLARE @NVR_GOODSUNIT AS NVARCHAR(32)
DECLARE @INT_UNIT INT
SELECT @NVR_SQL0='',@INT_ID=0,@NVR_SQL0='',@NVR_SQL1='',@NVR_SQL2='',@NVR_SQL3='',@NVR_SQL4='',@NVR_SQL5='',@NVR_SQL6='',@NVR_SQL7='',@NVR_SQL8='',@NVR_SQL9='',
@NVR_SQL10='',@NVR_SQL11='',@NVR_SQL12='',@NVR_SQL13='',@NVR_SQL14='',@NVR_SQL15='',@NVR_SQL16='',@NVR_SQL17='',@NVR_SQL18='',@NVR_SQL19='',@NVR_SQL20=''
DECLARE @INT_I INT
DECLARE @NVR_FLDNAME NVARCHAR(128)
SET @NVR_SQL0 = ' SELECT ' + @NVR_YCOL
-- 横向合计
DECLARE @NVR_SPTOTAL NVARCHAR(512)
DECLARE @INT_N AS INT
DECLARE @NVR_TOTALSQL NVARCHAR(512)
SELECT @INT_N=0,@NVR_TOTALSQL='',@NVR_TOTAL=RTRIM(LTRIM(ISNULL(@NVR_TOTAL,'')))
IF @NVR_TOTAL <>'' BEGIN
WHILE (1=1) BEGIN
SET @INT_N=@INT_N+1
SELECT @NVR_SPTOTAL= DBO.FUN_SplitStr(@NVR_TOTAL,@INT_N,',') -- 以逗号为分隔
SET @NVR_SPTOTAL=LTRIM(RTRIM(ISNULL(@NVR_SPTOTAL,'')))
IF @NVR_SPTOTAL <>'' BEGIN
IF @NVR_SPTOTAL='SUM' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [SUM]'
END ELSE IF @NVR_SPTOTAL='AVG' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [AVG]'
END ELSE IF @NVR_SPTOTAL='MAX' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [MAX]'
END ELSE IF @NVR_SPTOTAL='MIN' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [MIN]'
END
END ELSE BEGIN
BREAK
END
END
SET @NVR_SQL0=@NVR_SQL0 + @NVR_TOTALSQL
END
SET @NVR_CMD='DECLARE CORSS_CURSOR CURSOR FOR SELECT DISTINCT ' + @NVR_XCOL + ' FROM ' + @NVR_TABNAME + ' ' + @NVR_WHERE + ' ORDER BY ' + @NVR_XCOL + ' FOR READ ONLY ' --生成游标
EXECUTE (@NVR_CMD)
OPEN CORSS_CURSOR
WHILE (1=1) BEGIN
FETCH NEXT FROM CORSS_CURSOR INTO @NVR_XCOLNAME --遍历游标，将列头信息放入变量@NVR_XCOLNAME
IF (@@FETCH_STATUS <>0) BREAK
SET @INT_ID=@INT_ID+1
SET @NVR_XCOLNAME=REPLACE(@NVR_XCOLNAME ,CHAR(39),CHAR(39)+CHAR(39))
------------------------------------------------------------------------------
-- 可添加 数量,单价,金额 同时显示.单价= ( 金额 / 数量) .
IF @INT_ID <=50
SET @NVR_SQL1 = @NVR_SQL1 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=100
SET @NVR_SQL2 = @NVR_SQL2 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=150
SET @NVR_SQL3 = @NVR_SQL3 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=200
SET @NVR_SQL4 = @NVR_SQL4 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=250
SET @NVR_SQL5 = @NVR_SQL5 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=300
SET @NVR_SQL6 = @NVR_SQL6 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=350
SET @NVR_SQL7 = @NVR_SQL7 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=400
SET @NVR_SQL8 = @NVR_SQL8 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=450
SET @NVR_SQL9 = @NVR_SQL9 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=500
SET @NVR_SQL10 = @NVR_SQL10 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=550
SET @NVR_SQL11 = @NVR_SQL11 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=600
SET @NVR_SQL12 = @NVR_SQL12 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=650
SET @NVR_SQL13 = @NVR_SQL13 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=700
SET @NVR_SQL14 = @NVR_SQL14 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=750
SET @NVR_SQL15 = @NVR_SQL15 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=800
SET @NVR_SQL16 = @NVR_SQL16 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=850
SET @NVR_SQL17 = @NVR_SQL17 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=900
SET @NVR_SQL18 = @NVR_SQL18 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=950
SET @NVR_SQL19 = @NVR_SQL19 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=1000
SET @NVR_SQL20 = @NVR_SQL20 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
END -- WHILE (1 = 1)
--直接返回结果.
IF @NVR_ORDERBY='' BEGIN --未指定排序字段.
EXECUTE ( @NVR_SQL0 + @NVR_SQL1 + @NVR_SQL2 + @NVR_SQL3 + @NVR_SQL4 + @NVR_SQL5 + @NVR_SQL6 + @NVR_SQL7 + @NVR_SQL8 + @NVR_SQL9
+ @NVR_SQL10 + @NVR_SQL11 + @NVR_SQL12 + @NVR_SQL13 + @NVR_SQL14 + @NVR_SQL15 + @NVR_SQL16 + @NVR_SQL17 + @NVR_SQL18 + @NVR_SQL19 + @NVR_SQL20
+ N' FROM ' + @NVR_TABNAME + N' ' + @NVR_WHERE + N' GROUP BY ' + @NVR_YCOL ) -- 执行
END ELSE BEGIN --指定排序字段.
EXECUTE (N'SELECT * FROM (' + @NVR_SQL0 + @NVR_SQL1 + @NVR_SQL2 + @NVR_SQL3 + @NVR_SQL4 + @NVR_SQL5 + @NVR_SQL6 + @NVR_SQL7 + @NVR_SQL8 + @NVR_SQL9
+ @NVR_SQL10 + @NVR_SQL11 + @NVR_SQL12 + @NVR_SQL13 + @NVR_SQL14 + @NVR_SQL15 + @NVR_SQL16 + @NVR_SQL17 + @NVR_SQL18 + @NVR_SQL19 + @NVR_SQL20
+ N' FROM ' + @NVR_TABNAME + N' ' + @NVR_WHERE + N' GROUP BY ' + @NVR_YCOL + N') AS T86C6S9R ORDER BY ' + @NVR_ORDERBY ) -- 执行
END
CLOSE CORSS_CURSOR
DEALLOCATE CORSS_CURSOR
RETURN 0 --释放游标，返回0表示成功
END
GO
--
--下面用海关数据仓库的实例表,测试
--
EXECUTE PRC_CORSSQUERY_A 'DAT_2007','海关编号','商品编号,国家编号,地区行政编号','金额','WHERE 时间编号 BETWEEN 20070401 AND 20070401 AND 商品编号=''07131010_09'''
商品编号 国家编号 地区行政编号 22 37 51 52 95 97
------------------------ ---- ------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
07131010_09 111 310000 2156.0 NULL NULL NULL NULL NULL
07131010_09 116 350200 NULL 0.0 NULL NULL NULL NULL
07131010_09 116 620100 NULL NULL NULL NULL 0.0 NULL
07131010_09 116 630100 NULL NULL NULL NULL NULL 0.0
07131010_09 143 350200 NULL 4669.0 NULL NULL NULL NULL
07131010_09 143 440100 NULL NULL 6190.0 14400.0 NULL NULL
07131010_09 305 620100 NULL NULL NULL NULL 219.0 NULL
07131010_09 502 440100 NULL NULL 0.0 NULL NULL NULL
07131010_09 601 440100 NULL NULL 19185.0 NULL NULL NULL
（所影响的行数为 9 行）
-- 分段截取函数
----------------------------------------------------------------------------
CREATE FUNCTION DBO.FUN_SplitStr(
@S VARCHAR(8000), -- 包含多个数据项的字符串
@POS INT, -- 要获取的数据项的位置
@SPLIT VARCHAR(10) -- 数据分隔符
) RETURNS VARCHAR(128)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN INT
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @POS>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0 BEGIN
SELECT @POS=@POS-1,
@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
END
RETURN LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1)
END
GO
-------------------------------------------
--行列转换
-------------------------------------------
-------------------------------------------
--行列转换
-------------------------------------------
CREATE PROC PRC_CORSSQUERY
@NVR_TABNAME AS NVARCHAR(1024) = '', -- 此处放表名
@NVR_XCOL AS NVARCHAR(1024) = '', -- 表头分组依据字段 (横向字段,以系统编号作为依据,内部转化,此字段只能有一个)
@NVR_YCOL AS NVARCHAR(1024) = '', -- 分组字段 (纵向字段,直接取名称列,可以有多个,多个用逗号分隔)
@NVR_STATCOL AS NVARCHAR(1024) = '', -- 被统计的字段 (值)
@NVR_WHERE AS NVARCHAR(4000)='', -- WHERE条件 (一定要带 WHERE 语句,如:WHERE 地区=''广州'' ) * 注意字符一定要用两个分号.
@NVR_TOTAL AS NVARCHAR(1024)='', -- 合计的公式( SUM,AVG ,将其放在第一列.如果多项,请用逗号分隔)
@NVR_ORDERBY AS NVARCHAR(1024)='' -- 排序字段,如: [海关编码],[地区]
AS BEGIN
--SET NOCOUNT ON
DECLARE @NVR_CMD AS NVARCHAR(4000)
DECLARE @NVR_XCOLNAME AS NVARCHAR(256)
DECLARE @NVR_SQL0 AS NVARCHAR(4000)
DECLARE @NVR_SQL1 AS NVARCHAR(4000)
DECLARE @NVR_SQL2 AS NVARCHAR(4000)
DECLARE @NVR_SQL3 AS NVARCHAR(4000)
DECLARE @NVR_SQL4 AS NVARCHAR(4000)
DECLARE @NVR_SQL5 AS NVARCHAR(4000)
DECLARE @NVR_SQL6 AS NVARCHAR(4000)
DECLARE @NVR_SQL7 AS NVARCHAR(4000)
DECLARE @NVR_SQL8 AS NVARCHAR(4000)
DECLARE @NVR_SQL9 AS NVARCHAR(4000)
DECLARE @NVR_SQL10 AS NVARCHAR(4000)
DECLARE @NVR_SQL11 AS NVARCHAR(4000)
DECLARE @NVR_SQL12 AS NVARCHAR(4000)
DECLARE @NVR_SQL13 AS NVARCHAR(4000)
DECLARE @NVR_SQL14 AS NVARCHAR(4000)
DECLARE @NVR_SQL15 AS NVARCHAR(4000)
DECLARE @NVR_SQL16 AS NVARCHAR(4000)
DECLARE @NVR_SQL17 AS NVARCHAR(4000)
DECLARE @NVR_SQL18 AS NVARCHAR(4000)
DECLARE @NVR_SQL19 AS NVARCHAR(4000)
DECLARE @NVR_SQL20 AS NVARCHAR(4000)
DECLARE @NVR_DATETYPE AS NVARCHAR(1)
DECLARE @INT_ID AS INT
DECLARE @NVR_GOODSUNIT AS NVARCHAR(32)
DECLARE @INT_UNIT INT
SELECT @NVR_SQL0='',@INT_ID=0,@NVR_SQL0='',@NVR_SQL1='',@NVR_SQL2='',@NVR_SQL3='',@NVR_SQL4='',@NVR_SQL5='',@NVR_SQL6='',@NVR_SQL7='',@NVR_SQL8='',@NVR_SQL9='',
@NVR_SQL10='',@NVR_SQL11='',@NVR_SQL12='',@NVR_SQL13='',@NVR_SQL14='',@NVR_SQL15='',@NVR_SQL16='',@NVR_SQL17='',@NVR_SQL18='',@NVR_SQL19='',@NVR_SQL20=''
DECLARE @INT_I INT
DECLARE @NVR_FLDNAME NVARCHAR(128)
SET @NVR_SQL0 = ' SELECT ' + @NVR_YCOL
-- 横向合计
DECLARE @NVR_SPTOTAL NVARCHAR(512)
DECLARE @INT_N AS INT
DECLARE @NVR_TOTALSQL NVARCHAR(512)
SELECT @INT_N=0,@NVR_TOTALSQL='',@NVR_TOTAL=RTRIM(LTRIM(ISNULL(@NVR_TOTAL,'')))
IF @NVR_TOTAL <>'' BEGIN
WHILE (1=1) BEGIN
SET @INT_N=@INT_N+1
SELECT @NVR_SPTOTAL= DBO.FUN_SplitStr(@NVR_TOTAL,@INT_N,',') -- 以逗号为分隔
SET @NVR_SPTOTAL=LTRIM(RTRIM(ISNULL(@NVR_SPTOTAL,'')))
IF @NVR_SPTOTAL <>'' BEGIN
IF @NVR_SPTOTAL='SUM' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [SUM]'
END ELSE IF @NVR_SPTOTAL='AVG' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [AVG]'
END ELSE IF @NVR_SPTOTAL='MAX' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [MAX]'
END ELSE IF @NVR_SPTOTAL='MIN' BEGIN
SET @NVR_TOTALSQL=@NVR_TOTALSQL + ',' + @NVR_SPTOTAL + '(' + @NVR_STATCOL + ') AS [MIN]'
END
END ELSE BEGIN
BREAK
END
END
SET @NVR_SQL0=@NVR_SQL0 + @NVR_TOTALSQL
END
SET @NVR_CMD='DECLARE CORSS_CURSOR CURSOR FOR SELECT DISTINCT ' + @NVR_XCOL + ' FROM ' + @NVR_TABNAME + ' ' + @NVR_WHERE + ' ORDER BY ' + @NVR_XCOL + ' FOR READ ONLY ' --生成游标
EXECUTE (@NVR_CMD)
OPEN CORSS_CURSOR
WHILE (1=1) BEGIN
FETCH NEXT FROM CORSS_CURSOR INTO @NVR_XCOLNAME --遍历游标，将列头信息放入变量@NVR_XCOLNAME
IF (@@FETCH_STATUS <>0) BREAK
SET @INT_ID=@INT_ID+1
SET @NVR_XCOLNAME=REPLACE(@NVR_XCOLNAME ,CHAR(39),CHAR(39)+CHAR(39))
------------------------------------------------------------------------------
-- 可添加 数量,单价,金额 同时显示.单价= ( 金额 / 数量) .
IF @INT_ID <=50
SET @NVR_SQL1 = @NVR_SQL1 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=100
SET @NVR_SQL2 = @NVR_SQL2 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=150
SET @NVR_SQL3 = @NVR_SQL3 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=200
SET @NVR_SQL4 = @NVR_SQL4 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=250
SET @NVR_SQL5 = @NVR_SQL5 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=300
SET @NVR_SQL6 = @NVR_SQL6 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=350
SET @NVR_SQL7 = @NVR_SQL7 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=400
SET @NVR_SQL8 = @NVR_SQL8 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=450
SET @NVR_SQL9 = @NVR_SQL9 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=500
SET @NVR_SQL10 = @NVR_SQL10 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=550
SET @NVR_SQL11 = @NVR_SQL11 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=600
SET @NVR_SQL12 = @NVR_SQL12 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=650
SET @NVR_SQL13 = @NVR_SQL13 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=700
SET @NVR_SQL14 = @NVR_SQL14 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=750
SET @NVR_SQL15 = @NVR_SQL15 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=800
SET @NVR_SQL16 = @NVR_SQL16 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=850
SET @NVR_SQL17 = @NVR_SQL17 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=900
SET @NVR_SQL18 = @NVR_SQL18 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=950
SET @NVR_SQL19 = @NVR_SQL19 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
ELSE IF @INT_ID <=1000
SET @NVR_SQL20 = @NVR_SQL20 + ',SUM(CASE ' + @NVR_XCOL + ' WHEN ''' + @NVR_XCOLNAME + ''' THEN ' + @NVR_STATCOL + ' ELSE NULL END) AS [' + @NVR_XCOLNAME + ']' --构造查询
END -- WHILE (1 = 1)
--直接返回结果.
IF @NVR_ORDERBY='' BEGIN --未指定排序字段.
EXECUTE ( @NVR_SQL0 + @NVR_SQL1 + @NVR_SQL2 + @NVR_SQL3 + @NVR_SQL4 + @NVR_SQL5 + @NVR_SQL6 + @NVR_SQL7 + @NVR_SQL8 + @NVR_SQL9
+ @NVR_SQL10 + @NVR_SQL11 + @NVR_SQL12 + @NVR_SQL13 + @NVR_SQL14 + @NVR_SQL15 + @NVR_SQL16 + @NVR_SQL17 + @NVR_SQL18 + @NVR_SQL19 + @NVR_SQL20
+ N' FROM ' + @NVR_TABNAME + N' ' + @NVR_WHERE + N' GROUP BY ' + @NVR_YCOL ) -- 执行
END ELSE BEGIN --指定排序字段.
EXECUTE (N'SELECT * FROM (' + @NVR_SQL0 + @NVR_SQL1 + @NVR_SQL2 + @NVR_SQL3 + @NVR_SQL4 + @NVR_SQL5 + @NVR_SQL6 + @NVR_SQL7 + @NVR_SQL8 + @NVR_SQL9
+ @NVR_SQL10 + @NVR_SQL11 + @NVR_SQL12 + @NVR_SQL13 + @NVR_SQL14 + @NVR_SQL15 + @NVR_SQL16 + @NVR_SQL17 + @NVR_SQL18 + @NVR_SQL19 + @NVR_SQL20
+ N' FROM ' + @NVR_TABNAME + N' ' + @NVR_WHERE + N' GROUP BY ' + @NVR_YCOL + N') AS T86C6S9R ORDER BY ' + @NVR_ORDERBY ) -- 执行
END
CLOSE CORSS_CURSOR
DEALLOCATE CORSS_CURSOR
RETURN 0 --释放游标，返回0表示成功
END
GO
--
--下面用海关数据仓库的实例表,测试
--
EXECUTE PRC_CORSSQUERY_A 'DAT_2007','海关编号','商品编号,国家编号,地区行政编号','金额','WHERE 时间编号 BETWEEN 20070401 AND 20070401 AND 商品编号=''07131010_09'''
商品编号 国家编号 地区行政编号 22 37 51 52 95 97
------------------------ ---- ------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
07131010_09 111 310000 2156.0 NULL NULL NULL NULL NULL
07131010_09 116 350200 NULL 0.0 NULL NULL NULL NULL
07131010_09 116 620100 NULL NULL NULL NULL 0.0 NULL
07131010_09 116 630100 NULL NULL NULL NULL NULL 0.0
07131010_09 143 350200 NULL 4669.0 NULL NULL NULL NULL
07131010_09 143 440100 NULL NULL 6190.0 14400.0 NULL NULL
07131010_09 305 620100 NULL NULL NULL NULL 219.0 NULL
07131010_09 502 440100 NULL NULL 0.0 NULL NULL NULL
07131010_09 601 440100 NULL NULL 19185.0 NULL NULL NULL
（所影响的行数为 9 行）
