[原创] 按任意的字段旋转的存储过程 :)

----------------------------------------------------------------------------
-- 分段截取函数
----------------------------------------------------------------------------
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 @INT_I=0
WHILE (1=1) BEGIN
SET @INT_I=@INT_I+1
SELECT @NVR_FLDNAME= DBO.FUN_SplitStr(@NVR_YCOL,@INT_I,',') -- 以逗号为分隔
SET @NVR_FLDNAME=LTRIM(RTRIM(ISNULL(@NVR_FLDNAME,'')))
IF @NVR_FLDNAME<>'' BEGIN
SET @NVR_SQL0 =@NVR_SQL0 + @NVR_FLDNAME + ','
END ELSE BEGIN
SET @NVR_SQL0 = ' SELECT ' + LEFT(@NVR_SQL0 ,LEN(@NVR_SQL0)-1)
BREAK -- 结束拆分
END
END