存储过程"/>
自分定分组交叉透视存储过程
IF EXISTS (SELECT nameFROM sysobjects
WHERE name = N'p_pubcrosstable'
AND type = 'P')
DROP PROCEDURE p_pubcrosstable
GO
CREATE PROCEDURE p_pubcrosstable
(
@sourcesql varchar(8000), -- 需要交叉的sql
@sourcetable varchar(128), --需要交叉的table
@groupbyfields varchar(500), --分组字段
@colfields varchar(500), --列字段 可以多字段
@valuefields varchar(500), --统计值字段可以多字段
@isrowtotal bit = 1, --行统计
@issumtotal bit = 1, --列统计 暂不支持
@tag varchar(1000) = '' --<concatfieldname>xxx</concatfieldname> 分组合并字段 <iscount>1</iscount>计次 <insertintotablename>##abc</insertintotablename> 将结果保存到表名
)
AS
begin
/*
tsp 原创 支持SQL2000,
<concatfieldname>xxx</concatfieldname> 分组合并字段
部门 人数 姓名xxx
开发部 2 唐工;马工;
当列字段为多个时
部门 7月|期初人数 7月|新进人数 7月|离职人数 7月|初末人数
sunq 9 2 1 8
*/
declare @errormsg varchar(255), @iscount bit, @concatfieldname varchar(128)
set @groupbyfields = isnull(@groupbyfields, '')
set @colfields = isnull(@colfields, '')
set @valuefields = isnull(@valuefields, '')
set @groupbyfields = rtrim(ltrim(@groupbyfields))
set @colfields = rtrim(ltrim(@colfields))
set @valuefields = rtrim(ltrim(@valuefields))
set @iscount = 0
set @concatfieldname = ''
if @tag <> '' and dbo.f_analysestr(@tag, 'concatfieldname') <> ''
begin
set @concatfieldname = dbo.f_analysestr(@tag, 'concatfieldname')
end
if @tag <> '' and dbo.f_analysestr(@tag, 'iscount') = '1'
begin
set @iscount = 1
end
if @groupbyfields = '' or @colfields = '' or @valuefields = ''
begin
select @errormsg = dbo.f_geterror('99996') --99996 参数不正确
raiserror(@errormsg, 16, 1) with nowait
return -1
end
set @groupbyfields = replace(@groupbyfields, ';', ',')
set @colfields = replace(@colfields, ';', ',')
set @valuefields = replace(@valuefields, ';', ',')
if left(@groupbyfields, 1) = ',' set @groupbyfields = right(@groupbyfields, len(@groupbyfields) - 1)
if left(@colfields, 1) = ',' set @colfields = right(@colfields, len(@colfields) - 1)
if left(@valuefields, 1) = ',' set @valuefields = right(@valuefields, len(@valuefields) - 1)
if right(@groupbyfields, 1) = ',' set @groupbyfields = left(@groupbyfields, len(@groupbyfields) - 1)
if right(@colfields, 1) = ',' set @colfields = left(@colfields, len(@colfields) - 1)
if right(@valuefields, 1) = ',' set @valuefields = left(@valuefields, len(@valuefields) - 1)
declare @spid varchar(30), @sql varchar(8000), @nsql nvarchar(4000)
declare @rowcount int, @recordcount int, @recordcount2 int, @groupcount int, @colfieldcount int
set @spid = convert(varchar, @@spid)
declare @insertintotablename varchar(128), @sourcetemptablename varchar(128)
declare @distinctcoltablename varchar(128), @tablename_concat varchar(128)
declare @tablename_temp varchar(128)
set @insertintotablename = ''
set @sourcetemptablename = ''
set @tablename_concat = ''
set @sourcetemptablename = '##t_crosssource_sunq_' + @spid
--set @insertintotablename = '##t_crossresult_sunq_' + @spid
set @distinctcoltablename = '##t_crossdistinctcol_sunq_' + @spid
set @tablename_concat = '##t_crossconcat_sunq_' + @spid
if dbo.f_analysestr(@tag, 'insertintotablename') <> '' set @insertintotablename = dbo.f_analysestr(@tag, 'insertintotablename')
if exists (select * from tempdb.dbo.sysobjects where name = @sourcetemptablename)
begin
exec('drop table '+ @sourcetemptablename)
end
if exists (select * from tempdb.dbo.sysobjects where name = @distinctcoltablename)
begin
exec('drop table '+ @distinctcoltablename)
end
set @nsql = ' create table ' + @distinctcoltablename +
'(
colno int identity(1,1),
colvalue varchar(128)
) '
exec(@nsql)
if @@error <> 0 goto error
--生成源数据
if @sourcesql <> ''
begin
set @sql = 'select * into ' +@sourcetemptablename + ' from (' + @sourcesql + ') a'
exec(@sql)
if @@error <> '' goto error
end
else if @sourcetable <> ''
begin
set @sql = 'select * into ' +@sourcetemptablename + ' from ' + @sourcetable
exec(@sql)
if @@error <> '' goto error
end
declare @t_col_fields table
(
fieldno int,
fieldname varchar(128)
)
declare @t_value_fields table
(
fieldno int,
fieldname varchar(128)
)
--生成列字段
declare @i int, @p int, @no int, @gno int
declare @fieldname varchar(128), @fieldtype varchar(128)
declare @s varchar(8000), @strtemp varchar(3000)
declare @s1 varchar(8000), @s2 varchar(8000), @s3 varchar(8000)
set @strtemp = @colfields + ','
set @p = charindex(',' , @strtemp)
set @i = 0
while @p > 0
begin
set @fieldname = substring(@strtemp, 1, @p - 1)
set @i = @i + 1
insert into @t_col_fields(fieldno, fieldname) values(@i, @fieldname)
select @fieldtype = c.[name]
from tempdb.dbo.syscolumns a inner join tempdb.dbo.sysobjects b
on a.id = b.id inner join tempdb.dbo.systypes c
on a.xtype = c.xtype
where b.xtype = 'u' and b.name = @sourcetemptablename and a.name = @fieldname
set @nsql = ''
if @fieldtype in ('char', 'nchar', 'nvarchar', 'varchar')
begin
set @nsql = ' update ' + @sourcetemptablename + ' set ' + @fieldname + '='''''+
' where ' + @fieldname + ' is null '
end
else if @fieldtype in ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real')
begin
set @nsql = ' update ' + @sourcetemptablename + ' set ' + @fieldname + '=0'+
' where ' + @fieldname + ' is null '
end
else if @fieldtype in ('datetime', 'smalldatetime')
begin
set @nsql = ' update ' + @sourcetemptablename + ' set ' + @fieldname + '=0'+
' where ' + @fieldname + ' is null '
end
if @nsql <> '' exec sp_executesql @nsql
set @strtemp = substring(@strtemp, @p + 1, len(@strtemp) - @p)
set @p = charindex(',' , @strtemp)
end
set @strtemp = @valuefields + ','
set @p = charindex(',' , @strtemp)
set @i = 0
while @p > 0
begin
set @fieldname = substring(@strtemp, 1, @p - 1)
set @i = @i + 1
insert into @t_value_fields(fieldno, fieldname) values(@i, @fieldname)
set @strtemp = substring(@strtemp, @p + 1, len(@strtemp) - @p)
set @p = charindex(',' , @strtemp)
end
declare @t_sumcase_fields table
(
sqlno int,
sqlbody varchar(8000)
)
declare @t_createconcat_fields table
(
sqlno int,
sqlbody varchar(8000)
)
declare @t_selectconcat_fields table
(
sqlno int,
sqlbody_declare varchar(2000),
sqlbody_concat varchar(7000)
)
declare @t_updateconcat_fields table
(
sqlno int,
sqlbody varchar(8000)
)
select @colfieldcount = count(*) from @t_col_fields
if charindex('period', @colfields) > 0 and @colfieldcount = 1
begin
set @strtemp = @colfields
set @strtemp = replace(@strtemp, ',', '')
--set @strtemp = 'convert(varchar,' + @strtemp + ')'
set @nsql = ' insert into ' + @distinctcoltablename + '(colvalue)
select convert(varchar, colvalue)
from (select distinct ' + @strtemp + ' as colvalue from ' + @sourcetemptablename + ') a ' +
' order by colvalue '
end
else
begin
set @strtemp = @colfields
set @strtemp = replace(@strtemp, ',', ')+''|''+convert(varchar,')
set @strtemp = 'convert(varchar,' + @strtemp + ')'
set @nsql = ' insert into ' + @distinctcoltablename + '(colvalue)
select colvalue
from (select distinct ' + @strtemp + ' as colvalue from ' + @sourcetemptablename + ') a ' +
' order by colvalue '
end
--print @nsql
exec(@nsql)
if @@error <> 0 goto error
select @recordcount = count(*) from @t_value_fields
select @recordcount2 = 0
set @nsql = 'set @value = 0
select @value = count(*) from ' + @distinctcoltablename
exec sp_executesql @nsql, N'@value int output', @value = @recordcount2 output
if @recordcount2 >= 1024
begin
select @errormsg = dbo.f_geterror('99996') --99996 参数不正确 。
select @errormsg = @errormsg + ' field count >= 1024'
raiserror(@errormsg, 16, 1) with nowait
return -1
end
/*
exec(' select * from ' + @distinctcoltablename)
*/
--select @recordcount2
declare @bgno int, @edno int, @totalfieldname varchar(30)
declare @outcrossfields varchar(2000), @outcrossfields_detail varchar(2000)
set @no = 0
set @i = 0
set @outcrossfields = ''
set @outcrossfields_detail = ''
set @strtemp = @colfields
set @strtemp = replace(@strtemp, ',', ')+''''|''''+convert(varchar,')
set @strtemp = 'convert(varchar,' + @strtemp + ')'
select top 1 @i = fieldno, @fieldname = fieldname
from @t_value_fields
where fieldno > @i
order by fieldno
set @rowcount = @@rowcount
while @rowcount = 1
begin
--select @s = @s + ', sum(case convert(varchar,sex) when ''' + convert(varchar, colvalue) + ''' then emp_id else 0 end) as [' + case when convert(varchar, colvalue) = '' then 'null' else convert(varchar, colvalue) end + ']' from ##t_crossdistinctcol_sunq_64 where colno <= 60
--, sum(case convert(varchar,sex) when '' then emp_id else 0 end) as [null], sum(case convert(varchar,sex) when '男' then emp_id else 0 end) as [男], sum(case convert(varchar,sex) when '女' then emp_id else 0 end) as [女] + '_emp_id]'
set @totalfieldname = @fieldname
if @iscount = 1
set @totalfieldname = '1'
else
begin
select @fieldtype = c.[name]
from tempdb.dbo.syscolumns a inner join tempdb.dbo.sysobjects b
on a.id = b.id inner join tempdb.dbo.systypes c
on a.xtype = c.xtype
where b.xtype = 'u' and b.name = @sourcetemptablename and a.name = @fieldname
if @fieldtype in ('char', 'nchar', 'nvarchar', 'varchar') --字符型字段只计次
begin
set @totalfieldname = '1'
end
end
if @recordcount = 1
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + '']''' +
' from '+ @distinctcoltablename +
' order by colno '
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields output
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_' + @fieldname + ']''' +
' from '+ @distinctcoltablename +
' order by colno '
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields output
end
if @recordcount = 1
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_detailid]''' +
' from '+ @distinctcoltablename +
' order by colno '
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields_detail output
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_' + @fieldname + '_detailid]''' +
' from '+ @distinctcoltablename +
' order by colno '
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @outcrossfields_detail output
end
set @bgno = 1
while @bgno <= @recordcount2
begin
set @edno = @bgno + 30
if @recordcount = 1
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '',sum(case ' + @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' +
@totalfieldname + ' else 0 end) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + '']''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '',sum(case ' + @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' +
@totalfieldname + ' else 0 end) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_' + @fieldname + ']''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
end
--print @s
set @no = @no + 1
insert into @t_sumcase_fields(sqlno, sqlbody) values(@no, @s)
if @concatfieldname <> '' --合并字符串字段
begin
--create
if @recordcount = 1
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', convert(varchar(1000), space(0)) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_detailid]''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
if @@error <> 0 goto error
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', convert(varchar(1000), space(0)) as ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_' + @fieldname + '_detailid]''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
if @@error <> 0 goto error
end
insert into @t_createconcat_fields(sqlno, sqlbody) values(@no, @s)
--select
if @recordcount = 1
begin
set @s1 = ''
set @nsql = ' set @s = '''''+
' select @s = @s + '' declare @col'' + convert(varchar, colno) + ''_detailid varchar(500) ' +
' set @col'' + convert(varchar, colno) + ''_detailid = '''''''''''+
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s1 output
if @@error <> 0 goto error
set @nsql = ' set @s = '''''+
' select @s = @s + '', @col'' + convert(varchar, colno) + ''_detailid = '+
' case ' + @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then '+
' @col'' + convert(varchar, colno) + ''_detailid + ' + @concatfieldname + ' + '''';'''' else ' +
' @col'' + convert(varchar, colno) + ''_detailid end '''+
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s2 output
if @@error <> 0 goto error
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '' declare @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid varchar(500) ' +
' set @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid = '''''''''''+
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s1 output
if @@error <> 0 goto error
set @nsql = ' set @s = '''''+
' select @s = @s + '', @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid = ' +
' case ' + @strtemp + ' when '''''' + convert(varchar, colvalue) + '''''' then ' +
' @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid + ' + @concatfieldname + ' + '''';'''' else '+
' @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid end ''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s2 output
if @@error <> 0 goto error
end
set @s1 = stuff(@s1, 1, 1, '')
set @s2 = stuff(@s2, 1, 1, '')
insert into @t_selectconcat_fields(sqlno, sqlbody_declare, sqlbody_concat) values(@no, @s1, @s2)
--update
if @recordcount = 1
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_detailid] = ' +
'@col'' + convert(varchar, colno) + ''_detailid ''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
--print @s
if @@error <> 0 goto error
end
else
begin
set @nsql = ' set @s = '''''+
' select @s = @s + '', ['' + case when convert(varchar, colvalue) = '''' then ''null'' else convert(varchar, colvalue) end + ''_' + @fieldname + '_detailid] = ' +
' @col'' + convert(varchar, colno) + ''_' + @fieldname + '_detailid ''' +
' from '+ @distinctcoltablename +
' where colno >= ' + convert(varchar, @bgno) + ' and colno <= ' + convert(varchar, @edno)
--print @nsql
exec sp_executesql @nsql, N'@s varchar(8000) output ',@s = @s output
if @@error <> 0 goto error
end
set @s = stuff(@s, 1, 1, '')
insert into @t_updateconcat_fields(sqlno, sqlbody) values(@no, @s)
end
set @bgno = @edno + 1
end
--字段汇总
if @isrowtotal = 1
begin
if @recordcount = 1
begin
set @s = ',sum(' + @totalfieldname + ') as [rowtotal]'
end
else
begin
set @s = ',sum(' + @totalfieldname + ') as [rowtotal_' + @fieldname + ']'
end
set @no = @no + 1
insert into @t_sumcase_fields(sqlno, sqlbody) values(@no, @s)
end
select top 1 @i = fieldno, @fieldname = fieldname
from @t_value_fields
where fieldno > @i
order by fieldno
set @rowcount = @@rowcount
end
--select * from @t_selectconcat_fields
declare @sql_1 varchar(8000), @sql_2 varchar(8000), @sql_3 varchar(8000), @sql_4 varchar(8000)
declare @sql_5 varchar(8000), @sql_6 varchar(8000), @sql_7 varchar(8000), @sql_8 varchar(8000)
declare @sql_9 varchar(8000), @sql_10 varchar(8000), @sql_11 varchar(8000), @sql_12 varchar(8000)
select @sql_1 = '', @sql_2 = '', @sql_3 = '', @sql_4= '', @sql_5 = '', @sql_6 =''
select @sql_7 = '', @sql_8 = '', @sql_9 = '', @sql_10= '', @sql_12 = '', @sql_12 =''
declare @updateconcatsql varchar(8000), @sqlbody_declare varchar(8000), @sqlbody_concat varchar(8000)
declare @equefieldsql varchar(1000), @tempfieldsql varchar(1000), @strgno varchar(4)
set @equefieldsql = ''
set @tempfieldsql = @groupbyfields
while len(@tempfieldsql) > 0
begin
if charindex(',', @tempfieldsql) > 0
begin
set @fieldname = left(@tempfieldsql, charindex(',', @tempfieldsql)-1)
set @tempfieldsql = right(@tempfieldsql, len(@tempfieldsql)- charindex(',', @tempfieldsql))
end
else
begin
set @fieldname = @tempfieldsql
set @tempfieldsql = ''
end
if @equefieldsql <> '' set @equefieldsql = @equefieldsql + ' and '
set @equefieldsql = @equefieldsql + ' a.' + @fieldname + ' = b.' + @fieldname
end
if @concatfieldname <> '' --生成合并字符串表
begin
select @sql_1 = sqlbody from @t_createconcat_fields where sqlno = 1
select @sql_2 = sqlbody from @t_createconcat_fields where sqlno = 2
select @sql_3 = sqlbody from @t_createconcat_fields where sqlno = 3
select @sql_4 = sqlbody from @t_createconcat_fields where sqlno = 4
select @sql_5 = sqlbody from @t_createconcat_fields where sqlno = 5
select @sql_6 = sqlbody from @t_createconcat_fields where sqlno = 6
select @sql_7 = sqlbody from @t_createconcat_fields where sqlno = 7
select @sql_8 = sqlbody from @t_createconcat_fields where sqlno = 8
select @sql_9 = sqlbody from @t_createconcat_fields where sqlno = 9
select @sql_10 = sqlbody from @t_createconcat_fields where sqlno = 10
select @sql_11 = sqlbody from @t_createconcat_fields where sqlno = 11
select @sql_12 = sqlbody from @t_createconcat_fields where sqlno = 12
if exists (select * from tempdb.dbo.sysobjects where name = @tablename_concat)
begin
exec('drop table '+ @tablename_concat)
end
exec(' select identity(int, 1,1) as idno, ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 + @sql_6 +
@sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 +
' into ' + @tablename_concat +
' from ' + @sourcetemptablename +
' group by ' + @groupbyfields)
if @@error <> 0 goto error
set @nsql = 'set @value = 0
select @value = count(*) from ' + @tablename_concat
exec sp_executesql @nsql, N'@value int output', @value = @groupcount output
set @gno = 1
while @gno <= @groupcount --分组循环
begin
set @strgno = convert(varchar, @gno)
set @no = 0
select top 1 @no = sqlno, @updateconcatsql = sqlbody
from @t_updateconcat_fields
where sqlno > @no
order by sqlno
set @rowcount = @@rowcount
while @rowcount = 1
begin
select @sqlbody_declare = sqlbody_declare, @sqlbody_concat = sqlbody_concat
from @t_selectconcat_fields
where sqlno = @no
/*
print @sqlbody_declare + ' select ' + @sqlbody_concat +
' from ' + @tablename_concat + ' a inner join '+ @sourcetemptablename + ' b ' +
' on ' + @equefieldsql +
' where idno = ' + @strgno +
' update ' + @tablename_concat + ' set ' + @updateconcatsql +
' from ' + @tablename_concat + ' a '+
' where idno = ' + @strgno
*/
exec(@sqlbody_declare + ' select ' + @sqlbody_concat +
' from ' + @tablename_concat + ' a inner join '+ @sourcetemptablename + ' b ' +
' on ' + @equefieldsql +
' where idno = ' + @strgno +
' update ' + @tablename_concat + ' set ' + @updateconcatsql +
' from ' + @tablename_concat + ' a '+
' where idno = ' + @strgno)
if @@error <> 0 goto error
select top 1 @no = sqlno, @updateconcatsql = sqlbody
from @t_updateconcat_fields
where sqlno > @no
order by sqlno
set @rowcount = @@rowcount
end -- end while @rowcount = 1
set @gno = @gno + 1
end -- end while while @gno <= @groupcount
/*
exec(' select * from ' + @tablename_concat )
--*/
end
if exists (select * from tempdb.dbo.sysobjects where name = @insertintotablename)
begin
exec('drop table '+ @insertintotablename)
end
select @sql_1 = sqlbody from @t_sumcase_fields where sqlno = 1
select @sql_2 = sqlbody from @t_sumcase_fields where sqlno = 2
select @sql_3 = sqlbody from @t_sumcase_fields where sqlno = 3
select @sql_4 = sqlbody from @t_sumcase_fields where sqlno = 4
select @sql_5 = sqlbody from @t_sumcase_fields where sqlno = 5
select @sql_6 = sqlbody from @t_sumcase_fields where sqlno = 6
select @sql_7 = sqlbody from @t_sumcase_fields where sqlno = 7
select @sql_8 = sqlbody from @t_sumcase_fields where sqlno = 8
select @sql_9 = sqlbody from @t_sumcase_fields where sqlno = 9
select @sql_10 = sqlbody from @t_sumcase_fields where sqlno = 10
select @sql_11 = sqlbody from @t_sumcase_fields where sqlno = 11
select @sql_12 = sqlbody from @t_sumcase_fields where sqlno = 12
if @concatfieldname = ''
begin
if @insertintotablename <> ''
begin
exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 + @sql_6 +
@sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 +
' into ' + @insertintotablename +
' from ' + @sourcetemptablename +
' group by ' + @groupbyfields)
end
else
begin
exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 + @sql_6 +
@sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 +
' from ' + @sourcetemptablename +
' group by ' + @groupbyfields)
end
if @@error <> 0 goto error
end
else
begin
set @tablename_temp = '##crosstable_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称
if exists (select * from tempdb.dbo.sysobjects where name= @tablename_temp)
begin
exec('drop table ' + @tablename_temp)
end
exec(' select ' + @groupbyfields + @sql_1 + @sql_2 + @sql_3 + @sql_4 + @sql_5 + @sql_6 +
@sql_7 + @sql_8 + @sql_9 + @sql_10 + @sql_11 + @sql_12 +
' into ' + @tablename_temp +
' from ' + @sourcetemptablename +
' group by ' + @groupbyfields)
if @@error <> 0 goto error
--exec(' select * from ' + @tablename_temp )
set @strtemp = ',' + @groupbyfields
set @strtemp = replace(@strtemp, ' ', '')
set @strtemp = replace(@strtemp, ',', 'a.')
if @insertintotablename <> ''
begin
exec(' select ' + @strtemp + @outcrossfields + @outcrossfields_detail +
' into ' + @insertintotablename +
' from ' + @tablename_temp + ' a inner join ' + @tablename_concat + ' b ' +
' on ' + @equefieldsql)
end
else
begin
exec(' select '+ @strtemp + @outcrossfields + @outcrossfields_detail +
' from ' + @tablename_temp + ' a inner join ' + @tablename_concat + ' b ' +
' on ' + @equefieldsql)
end
if @@error <> 0 goto error
if exists (select * from tempdb.dbo.sysobjects where name= @tablename_temp)
begin
exec('drop table ' + @tablename_temp)
end
end
return 0
error:
begin
select @errormsg = dbo.f_geterror('99999') --99999 系统内部错误。
raiserror(@errormsg, 16, 1) with nowait
return -1
end
end
go
/*
select * from dbo.t_sys_errorcode
exec p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'emp_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'sex', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'workno', 'emp_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'sex', 'emp_id,dept_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'sex,gzcls', 'emp_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id,dept_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'sex,gzclsname,gradename,position,native', 'emp_id,dept_id', ''
exec p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id'
exec p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 1, 0, '<iscount>1</iscount>'
exec p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 0, 0, '<iscount>1</iscount><concatfieldname>cname</concatfieldname>'
exec p_pubcrosstable '', 'vw_employee', 'simname', 'position', 'emp_id', 0, 0, '<insertintotablename>##abc</insertintotablename><iscount>1</iscount><concatfieldname>workno</concatfieldname>'
exec p_pubcrosstable '', 'vw_employee', 'simname', 'gzclsname,sex', 'emp_id,dept_id', 0, 0, '<iscount>1</iscount><concatfieldname>workno</concatfieldname>'
exec p_pubcrosstable ' select workno, cname, simname, position, effectday, gzcls, kqtypename,##kqtotalanalyseitem_tj.itemname,##hrperiob_tj.periobname, totalvalue from ##kqtotalanalyse_tj left join ##hrperiob_tj on ##kqtotalanalyse_tj.timeunit = ##hrperiob_tj.periobsn left join ##kqtotalanalyseitem_tj on ##kqtotalanalyse_tj.totalitem = ##kqtotalanalyseitem_tj.itemcode group by workno, cname, simname, position, effectday, gzcls, kqtypename, ##kqtotalanalyseitem_tj.itemname, ##hrperiob_tj.periobname, totalvalue ', '', 'workno;cname;simname;position;effectday;gzcls;kqtypename;itemname', 'periobname;', 'totalvalue', 0, 0, '<INSERTINTOTABLENAME>##cross_sunq_57</INSERTINTOTABLENAME>'
exec p_pubcrosstable ' select workno, cname, simname, position, effectday, gzcls, kqtypename,##kqtotalanalyseitem_tj.itemname,##hrperiob_tj.periobname, totalvalue from ##kqtotalanalyse_tj left join ##hrperiob_tj on ##kqtotalanalyse_tj.timeunit = ##hrperiob_tj.periobsn left join ##kqtotalanalyseitem_tj on ##kqtotalanalyse_tj.totalitem = ##kqtotalanalyseitem_tj.itemcode group by workno, cname, simname, position, effectday, gzcls, kqtypename, ##kqtotalanalyseitem_tj.itemname, ##hrperiob_tj.periobname, totalvalue ', '', 'workno;cname;simname;position;effectday;gzcls;itemname', 'periobname;kqtypename;', 'totalvalue', 0, 0, '<INSERTINTOTABLENAME>##cross_sunq_57</INSERTINTOTABLENAME>'
*/
更多推荐
自分定分组交叉透视存储过程
发布评论