declare @str_sql varchar(8000)
set @str_sql='select [ID]'
select @str_sql=@str_sql+',max(case KhZtID when '+[ID]+' then '*' else '' end) As '+KhZt+''
from KhZt
select @str_sql=@str_sql+'from KhZy group by [ID]'
exec(@str_sql)
set @str_sql='select [ID]'
select @str_sql=@str_sql+',max(case KhZtID when '+[ID]+' then '*' else '' end) As '+KhZt+''
from KhZt
select @str_sql=@str_sql+'from KhZy group by [ID]'
exec(@str_sql)
(
ID varchar(20),
KhMc varchar(20),
KhZtID varchar(20)
)
create table khZT
(
ID varchar(20),
KhZt varchar(20),
BZ varchar(20)
)
insert KhZy
select '0001','客户1','02' union
select '0002','客户2','03' union
select '0003','客户3','01'
insert KhZT
select '01','状态1','' union
select '02','状态2','' union
select '03','状态3',''
go--查询
declare @sql varchar(8000),@sql2 varchar(8000)
select @sql='',@sql2=''
select @sql=@sql+',max(case when B.KhZt='''+KhZt+''' then ''*'' else '''' end) as '''+KhZt+''''
,@sql2=@sql2+',convert(varchar,sum(case when B.KhZt='''+KhZt+''' then 1 else 0 end))'
from khZT group by KhZt
select @sql='select A.KhMc'+@sql+' from khZy A join khZt B on A.KhZtID=B.ID group by A.KhMc'
+' union all'
+' select ''合计'''+@sql2+' from khZy A join khZt B on A.KhZtID=B.ID '
exec(@sql)--删除测试环境
drop table khZy,khZT--结果
/*
KhMc 状态1 状态2 状态3
-------------------- ------------------------------ ---------
客户1 *
客户2 *
客户3 *
合计 1 1 1
*/
set @str_sql='select [ID]'
select @str_sql=@str_sql+',max(case KhZtID when '''+[ID]+''' then ''*'' else '''' end) As '+KhZt+''
from KhZt
select @str_sql=@str_sql+' from KhZy group by [ID]'
exec(@str_sql)
SELECT '0001' , '客户1' , '02'
UNION ALL SELECT '0002' , '客户2' , '03'
UNION ALL SELECT '0003', '客户3', '01'create table KhZt(ID VARCHAR(50),KhZt VARCHAR(50),BZ VARCHAR(50))
INSERT INTO KhZt(ID,KhZt)
SELECT '01' , '状态1'
UNION ALL SELECT '02' , '状态2'
UNION ALL SELECT '03', '状态3'declare @s1 nvarchar(4000)
select @s1=''select @s1=@s1+','+b.KhZt+'=max(case when b.KhZt='''+b.KhZt+''' then ''*'' else '''' end)'
from KhZy a,KhZt b WHERE a.KhZtID=b.ID
group by b.KhZtselect @s1='select KhMc' + @s1 + ' from KhZy a,KhZt b WHERE a.KhZtID=b.ID
group by KhMc'
exec(@s1)
drop table KhZy,KhZt
(
ID varchar(20),
KhMc varchar(20),
KhZtID varchar(20)
)
create table khZT
(
ID varchar(20),
KhZt varchar(20),
BZ varchar(20)
)
insert KhZy
select '0001','客户1','02' union
select '0002','客户2','03' union
select '0003','客户3','01'
insert KhZT
select '01','状态1','' union
select '02','状态2','' union
select '03','状态3',''
go--查询
declare @sql varchar(8000),@sql2 varchar(8000)
select @sql='',@sql2=''
select @sql=@sql+',max(case when B.KhZt='''+KhZt+''' then ''*'' else '''' end) as '''+KhZt+''''
,@sql2=@sql2+',convert(varchar,sum(case when B.KhZt='''+KhZt+''' then 1 else 0 end))'
from khZT group by KhZt
select @sql='select A.KhMc'+@sql+',count(1) as ''合计'' from khZy A join khZt B on A.KhZtID=B.ID group by A.KhMc'
+' union all'
+' select ''合计'''+@sql2+',count(1) from khZy A join khZt B on A.KhZtID=B.ID '
exec(@sql)--删除测试环境
drop table khZy,khZT--结果
/*
KhMc 状态1 状态2 状态3 合计
-------------------- ------------------------------ ----------
客户1 * 1
客户2 * 1
客户3 * 1
合计 1 1 1 3
*/
([id] varchar(20),khmc varchar(20),khztid varchar(20))[create] table t2
([id] varchar(20),khzt varchar(20),备注 varchar(20))insert t1
select '0001','客户1','02' [union] all
select '0002','客户2','03' [union] all
select '0003','客户3','01' insert t2
select '01','状态1','' [union] all
select '02','状态2','' [union] all
select '03','状态3',''declare @sql varchar(2000),@c varchar(2000),@c1 varchar(2000)set @sql =''
set @c=''
set @c1=''
select @sql=@sql+',['+khzt+']=max(case when khzt='''+khzt+''' then ''*'' else '''' end)',
@c=@c+',['+khzt+']=isnull(max(case when khzt='''+khzt+''' then 1 end),0)',
@c1=@c1+',cast(sum('+khzt+') as varchar)'
from
(select a.[id],a.khmc,b.khzt from t1 a,t2 b where a.khztid=b.[id]) a
group by khzt
order by khztexec('select khmc'+@sql+' from
(select a.[id],a.khmc,b.khzt from t1 a,t2 b where a.khztid=b.[id]) a
group by khmc
union all
select ''合计'''+@c1+' from(
select khmc'+@c+' from
(select a.[id],a.khmc,b.khzt from t1 a,t2 b where a.khztid=b.[id]) a
group by khmc) a ')drop table t1
drop table t2khmc 状态1 状态2 状态3
---------- ------------------ ------------------ ----------------------
客户1 *
客户2 *
客户3 *
合计 1 1 1警告: 聚合或其它 SET 操作消除了空值。
收下!!以后要写类似的就拿来改一下就是了,难得自己写了哈1!!!嘿嘿!!但是如果数据量非常大的话(Sql超过8000),就不能用这个了。不过可以用临时表来弄!!
思路如下!!用Create Table 生成一个临时表,
然后创建游标,用Alert Table 语句向临时表加字段
接下来就是再用游标向临时表Insert 记录
最后从临时表Select 出来!!非常麻烦!!而且这么一折腾,数据库开销很大!!不晓得还有不有更好的办法!!