34,590
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#t1') is null
drop table #t1
Go
Create table #t1([ID] int,[F1] nvarchar(2))
Insert #t1
select 1,N'aa' union all
select 2,N'bb'
Go
if not object_id(N'Tempdb..#t2') is null
drop table #t2
Go
Create table #t2([t1_ID] int,[unit] nvarchar(3),[Num] INT,price MONEY)
Insert #t2
select 1,N'公司1',5 ,100union all
select 1,N'公司2',10,20 union all
select 2,N'公司1',1000,5 union all
select 2,N'公司2',2000,3
Go
declare @Sql nvarchar(max)='',@Sql2 NVARCHAR(max)=''
select @Sql= @Sql+','+quotename(unit+'Num')+'=sum(case when b.unit='+QUOTENAME(unit,'''')+' then Num else 0 END) '
,@Sql2= @Sql2+','+quotename(unit+'price')+'=sum(case when b.unit='+QUOTENAME(unit,'''')+' then price else 0 END) '
from #t2 group by unit
EXEC('SELECT a.ID,a.F1'+@Sql+@Sql2+' FROM #t1 AS a inner join #t2 as b on a.ID=b.t1_id group by a.ID,a.F1')
/*
ID F1 公司1Num 公司2Num 公司1price 公司2price
1 aa 5 10 100.00 20.00
2 bb 1000 2000 5.00 3.00
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#t1') is null
drop table #t1
Go
Create table #t1([ID] int,[F1] nvarchar(2))
Insert #t1
select 1,N'aa' union all
select 2,N'bb'
Go
if not object_id(N'Tempdb..#t2') is null
drop table #t2
Go
Create table #t2([t1_ID] int,[unit] nvarchar(3),[Num] int)
Insert #t2
select 1,N'公司1',5 union all
select 1,N'公司2',10 union all
select 2,N'公司1',1000 union all
select 2,N'公司2',2000
Go
declare @Sql nvarchar(max)=''
select @Sql= @Sql+','+quotename(unit+'Num')+'=sum(case when b.unit='+QUOTENAME(unit,'''')+' then Num else 0 END) ' from #t2 group by unit
EXEC('SELECT a.ID,a.F1'+@Sql+' FROM #t1 AS a inner join #t2 as b on a.ID=b.t1_id group by a.ID,a.F1')
/*
ID F1 公司1Num 公司2Num
1 aa 5 10
2 bb 1000 2000
*/
declare @Sql nvarchar(max)=''
select @Sql= @Sql+','+quotename(unit+'Num')+'=sum(case when b.unit='+QUOTENAME(unit,'''')+' then Num else 0 END) ' from T2 group by unit
EXEC('SELECT a.ID,a.F1'+@Sql+' FROM T1 AS a inner join T2 as b on a.ID=b.t1_id group by a.ID,a.F1')