22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#tb') is not null drop table #tb
go
Create table #tb(类别 NVARCHAR(100),期初数 int,入库数 int,出库数 INT,期末数 INT)
Insert #tb
select N'草药',1,2,1,2 UNION all
select N'西药',2,2,1,3
declare @cat nvarchar(max),@sql nvarchar(max)
select @cat=isnull(@cat,N'')+N'SUM(CASE WHEN t.类别=N'''+类别+N''' THEN c.v ELSE 0 END) AS '+类别+N',' from #tb group by 类别
set @sql=N'
select c.账类名称,'+@cat+N'sum(c.v) as 合计 from #tb as t
cross apply(values(1,N''期初数'',期初数),(2,N''入库数'',入库数),(3,N''出库数'',出库数),(4,N''期末数'',期末数)) c(ID,账类名称,v)
group by c.ID,c.账类名称
order by c.ID'
select @sql
EXEC(@sql)
账类名称 草药 西药 合计
1 期初数 1 2 3
2 入库数 2 2 4
3 出库数 1 1 2
4 期末数 2 3 5
动态生成的语句如下:
select c.账类名称,SUM(CASE WHEN t.类别=N'草药' THEN c.v ELSE 0 END) AS 草药,SUM(CASE WHEN t.类别=N'西药' THEN c.v ELSE 0 END) AS 西药,sum(c.v) as 合计 from #tb as t
cross apply(values(1,N'期初数',期初数),(2,N'入库数',入库数),(3,N'出库数',出库数),(4,N'期末数',期末数)) c(ID,账类名称,v)
group by c.ID,c.账类名称
order by c.ID
--测试数据
if not object_id('tab') is null
drop table tab
Go
Create table tab(类别 NVARCHAR(100),期初数 int,入库数 int,出库数 INT,期末数 INT)
Insert tab
select '草药',1,2,1,2 UNION all
select '西药',2,2,1,3
go
--测试数据结束
DECLARE @s NVARCHAR(4000) ,
@s2 NVARCHAR(4000) ,
@s3 NVARCHAR(4000) ,
@s4 NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', 'declare ') + '@' + RTRIM(Colid)
+ ' nvarchar(4000)' ,
@s2 = ISNULL(@s2 + ',', 'select ') + '@' + RTRIM(Colid) + '='''
+ CASE WHEN @s2 IS NOT NULL THEN 'union all select'
ELSE ' select '
END + ' 账类名称=''' + QUOTENAME(Name, '''') + '''''' ,
@s3 = ISNULL(@s3, '') + 'select @' + RTRIM(Colid) + '=@' + RTRIM(Colid)
+ '+'',''+quotename([类别])+''=''+quotename(' + QUOTENAME(Name)
+ ','''''''') from tab ' ,
@s4 = ISNULL(@s4 + '+', '') + '@' + RTRIM(Colid)
FROM syscolumns
WHERE id = OBJECT_ID('tab')
AND Name NOT IN ( '类别' )
EXEC(@s+' '+@s2+' '+@s3+' exec(''SELECT *, CONVERT(INT,t.草药)+CONVERT(INT,t.西药) AS 合计 FROM (''+'+@s4+'+'')t'')');