转皱大哥的例子
/*------一个标准的统计交叉表过程-----------*/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
/*------一个标准的统计交叉表过程-----------*/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
select ProductCode,
[B/S]=max(case when MaterialClassName='B/S'
then MaterialName else '' end),
[C/L]=max(case when MaterialClassName='C/L'
then MaterialName else '' end),
[S] =max(case when MaterialClassName='S'
then MaterialName else '' end)
from tbl
group by ProductCode
declare @s varchar(2000)
set @s='select ProductCode'
select @s=@s+',['+MaterialClassName+']=max(case when MaterialClassName='''+MaterialClassName+'''then MaterialName end) '
from 表名
group by MaterialClassName
set @s=@s+' from 表名 group by ProductCode'
print @s
exec(@s)
不好意思,是我不小心漏掉了。您观察很仔细,05387的记录也是需要的。
谢谢您!
create table t(ProductCode varchar(20),MaterialClassName varchar(20),MaterialName varchar(60))
insert into t select '05387',rtrim('B/S'),rtrim('BA-1Z03AE(1/2)N ')
insert into t select '05387',rtrim('C/L'),rtrim('K-1V15A-38(セパ)')
insert into t select '05387',rtrim('C/L'),rtrim('K-1V15A-38(セパ)')
insert into t select '05387',rtrim('V '),rtrim('KEL-GEF 0.3t NA-51.')
insert into t select '05628',rtrim('B/S'),rtrim('SB18-25-18CE')
insert into t select '05628',rtrim('C/L'),rtrim('K-1/2A15A-38(セパ)')
insert into t select '05628',rtrim('C/L'),rtrim('K-1/2A15A-38(セパ)')
insert into t select '05628',rtrim('S '),rtrim('K-2U25K-60(TO) PET2mil(No.11)SPV')
insert into t select '05208',rtrim('B/S'),rtrim('BA-1/2T15AE(1/2)')
insert into t select '05208',rtrim('C/L'),rtrim('K-1/2N15A-38(セパ)')
insert into t select '05208',rtrim('C/L'),rtrim('K-1/2N15A-38(セパ)')
insert into t select '05208',rtrim('S '),rtrim('K-5U20K/K-2U25K-60(TO) PET2mil(No.11)SPV')
insert into t select '05484',rtrim('B/S'),rtrim('SB18-25-18CE')
insert into t select '05484',rtrim('C/L'),rtrim('K-1/2N15A-38(セパ)')
insert into t select '05484',rtrim('C/L'),rtrim('K-1/2N15A-38(セパ)')
insert into t select '05484',rtrim('S '),rtrim('K-5U20K/K-5U20K-60(TO)<J类型> PET2mil(No.11)SPV')
insert into t select '05484',rtrim('S '),rtrim('K-1U20K-60(TO)J类型')
insert into t select '05608',rtrim('B/S'),rtrim('SB18-25-18CE')
insert into t select '05608',rtrim('C/L'),rtrim('K-1/2A15A-38(セパ)')
insert into t select '05608',rtrim('C/L'),rtrim('K-1/2A15A-38(セパ)')
insert into t select '05608',rtrim('S '),rtrim('K-5U25K-60(TO)/SPV')
insert into t select '05535',rtrim('B/S'),rtrim('SB18-25-18CE')
go--创建用户定义函数
create function f_str(@ProductCode varchar(10),@MaterialClassName varchar(10))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+' '+MaterialName from t
where
ProductCode=@ProductCode
and
MaterialClassName=@MaterialClassName
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--借助用户定义函数实现动态交叉表查询,结果自己看
declare @s varchar(8000)
set @s = 'select ProductCode'select @s = @s+',['+MaterialClassName+']=dbo.f_str(ProductCode,'''+MaterialClassName+''')'
from t group by MaterialClassNameset @s = @s + ' from t group by ProductCode order by ProductCode'
exec(@s)--删除测试数据
drop function f_str
drop table t
我的C/L、S有些是出现了多条记录的,而上面的代码却只选了最后一条,即使是重复的,也要显示!
go
insert into test1 select '05387','B/S','BA-1Z03AE(1/2)N'
union all select '05387','C/L','K-1V15A-38(セパ)'
union all select '05387','C/L','K-1V15A-38(セパ)'
union all select '05387','V','KEL-GEF 0.3t NA-51.'
union all select '05628','B/S','SB18-25-18CE'
union all select '05628','C/L','K-1/2A15A-38(セパ)'
union all select '05628','C/L','K-1/2A15A-38(セパ)'
union all select '05628','S','K-2U25K-60(TO) PET2mil(No.11)SPV'
union all select '05484','B/S','SB18-25-18CE'
union all select '05484','C/L','K-1/2N15A-38(セパ)'
union all select '05484','C/L','K-1/2N15A-38(セパ)'
union all select '05484','S','K-5U20K/K-5U20K-60(TO)<J类型> PET2mil(No.11)SPV'
union all select '05484','S','K-1U20K-60(TO)J类型'
union all select '05608','B/S','SB18-25-18CE'
union all select '05608','C/L','K-1/2A15A-38(セパ)'
union all select '05608','C/L','K-1/2A15A-38(セパ)'
union all select '05608','S','K-5U25K-60(TO)/SPV'
union all select '05535','B/S','SB18-25-18CE'
go
create function fnc_test1(@ProductCode varchar(10),@ClassName varchar(10))
returns nvarchar(100)
as
begin
declare @rlt nvarchar(100)
set @rlt=''
select @rlt=@rlt+' '+MaterialName from test1 where ProductCode=@ProductCode and MaterialClassName=@ClassName
return @rlt
end
go
select ProductCode,dbo.fnc_test1(ProductCode,'B/S'),
dbo.fnc_test1(ProductCode,'C/L'),dbo.fnc_test1(ProductCode,'S') from test1 group by ProductCode
--测试结果
ProductCode
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
05387 BA-1Z03AE(1/2)N K-1V15A-38(セパ) K-1V15A-38(セパ)
05484 SB18-25-18CE K-1/2N15A-38(セパ) K-1/2N15A-38(セパ) K-5U20K/K-5U20K-60(TO)<J类型> PET2mil(No.11)SPV K-1U20K-60(TO)J类型
05535 SB18-25-18CE
05608 SB18-25-18CE K-1/2A15A-38(セパ) K-1/2A15A-38(セパ) K-5U25K-60(TO)/SPV
05628 SB18-25-18CE K-1/2A15A-38(セパ) K-1/2A15A-38(セパ) K-2U25K-60(TO) PET2mil(No.11)SPV
go
insert into test1 select '05387','B/S','BA-1Z03AE(1/2)N'
union all select '05387','C/L','K-1V15A-38(セパ)'
union all select '05387','C/L','K-1V15A-38(セパ)'
union all select '05387','V','KEL-GEF 0.3t NA-51.'
union all select '05628','B/S','SB18-25-18CE'
union all select '05628','C/L','K-1/2A15A-38(セパ)'
union all select '05628','C/L','K-1/2A15A-38(セパ)'
union all select '05628','S','K-2U25K-60(TO) PET2mil(No.11)SPV'
union all select '05208','B/S','BA-1/2T15AE(1/2)'
union all select '05208','C/L','K-1/2N15A-38(セパ)'
union all select '05208','C/L','K-1/2N15A-38(セパ)'
union all select '05208','S','K-5U20K/K-2U25K-60(TO) PET2mil(No.11)SPV'
union all select '05484','B/S','SB18-25-18CE'
union all select '05484','C/L','K-1/2N15A-38(セパ)'
union all select '05484','C/L','K-1/2N15A-38(セパ)'
union all select '05484','S','K-5U20K/K-5U20K-60(TO)<J类型> PET2mil(No.11)SPV'
union all select '05484','S','K-1U20K-60(TO)J类型'
union all select '05608','B/S','SB18-25-18CE'
union all select '05608','C/L','K-1/2A15A-38(セパ)'
union all select '05608','C/L','K-1/2A15A-38(セパ)'
union all select '05608','S','K-5U25K-60(TO)/SPV'
union all select '05535','B/S','SB18-25-18CE'
go
create function fnc_test1(@ProductCode varchar(10),@ClassName varchar(10))
returns nvarchar(100)
as
begin
declare @rlt nvarchar(100)
set @rlt=''
select @rlt=@rlt+' '+MaterialName from test1 where ProductCode=@ProductCode and MaterialClassName=@ClassName
return @rlt
end
go
select ProductCode,dbo.fnc_test1(ProductCode,'B/S'),
dbo.fnc_test1(ProductCode,'C/L'),dbo.fnc_test1(ProductCode,'S') from test1 group by ProductCode
go
--测试结果
ProductCode
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
05208 BA-1/2T15AE(1/2) K-1/2N15A-38(セパ) K-1/2N15A-38(セパ) K-5U20K/K-2U25K-60(TO) PET2mil(No.11)SPV
05387 BA-1Z03AE(1/2)N K-1V15A-38(セパ) K-1V15A-38(セパ)
05484 SB18-25-18CE K-1/2N15A-38(セパ) K-1/2N15A-38(セパ) K-5U20K/K-5U20K-60(TO)<J类型> PET2mil(No.11)SPV K-1U20K-60(TO)J类型
05535 SB18-25-18CE
05608 SB18-25-18CE K-1/2A15A-38(セパ) K-1/2A15A-38(セパ) K-5U25K-60(TO)/SPV
05628 SB18-25-18CE K-1/2A15A-38(セパ) K-1/2A15A-38(セパ) K-2U25K-60(TO) PET2mil(No.11)SPV(所影响的行数为 6 行)