27,579
社区成员
发帖
与我相关
我的任务
分享
create table #t
(供应商 varchar(6),型号 varchar(6), 规格 varchar(6),
[9-1_in] int, [9-1_out] int, [9-2_in] int, [9-2_out] int)
insert into #t
select 'a1', 'x1', '箱', null,10,null,null union all
select 'a1', 'x1', '箱', 5,2,null,20 union all
select 'a2', 'y1', '捆', null,null,8,null union all
select 'a2', 'y1', '捆', 15,null,null,null
declare @tsql varchar(6000)
select @tsql='select 供应商,型号,规格,'
+stuff(
(select ',sum(case when ['+b.name+'] is not null then ['+b.name+'] else 0 end) '''+b.name+''' '
from tempdb.sys.tables a
inner join tempdb.sys.columns b on a.object_id=b.object_id
where a.object_id=object_id('tempdb..#t')
and b.name not in('供应商','型号','规格') for xml path('')),1,1,'')
+' from #t group by 供应商,型号,规格'
exec(@tsql)
/*
供应商 型号 规格 9-1_in 9-1_out 9-2_in 9-2_out
------ ------ ------ ----------- ----------- ----------- -----------
a1 x1 箱 5 12 0 20
a2 y1 捆 15 0 8 0
(2 row(s) affected)
*/
declare @tsql varchar(6000)
select @tsql='select 供应商,型号,规格,'
+stuff(
(select ',sum(case when '+b.name+' is not null then '+b.name+' else 0 end) '''+b.name+''' '
from tempdb.sys.tables a
inner join tempdb.sys.columns b on a.object_id=b.object_id
where a.object_id=object_id('tempdb..#t')
and b.name not in('供应商','型号','规格') for xml path('')),1,1,'')
+' from #t group by 供应商,型号,规格'
exec(@tsql)