sqlserver动态行转列 invid csitemname ciscost precents (该表为临时表,precents为自己加的,为了算比率,不知是否合适)
1233 材料成本 20 0
1233 加工成本 40 0
1233 委外加工 30 0
1233 人工费用 20 0
1255 材料成本 20 0
想要的结果:
invid 材料成本 precents 加工成本 precents 委外加工 precents 人工费用 precents
1233 20 20/(20+40+30+20) 40 40/(20+40+30+20) 30 30/(20+40+30+20) 20 20/(20+40+30+20)
1255 20 20/20
其中csitemname为另一张表的信息,数据个数可能会变。 csitemname 需和precents配对出现
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([invid] int,[csitemname] nvarchar(4),[ciscost] int,[precents] int)
Insert #T
select 1233,N'材料成本',20,0 union all
select 1233,N'加工成本',40,0 union all
select 1233,N'委外加工',30,0 union all
select 1233,N'人工费用',20,0 union all
select 1255,N'材料成本',20,0
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(csitemname)+N'=str(sum(case when csitemname=N'+quotename(csitemname,'''')+N' then [ciscost] else 0 end)*100.0/b.sumCiscost,5,2)+''%'''from #T group by csitemname
--顯示生成語句
print N'select a.invid'+@s+N' from #T as a
inner join (select invid,sum([ciscost]) as sumCiscost from #T group by invid)as b on a.invid=b.invid group by a.invid,b.sumCiscost'exec(N'select a.invid'+@s+N' from #T as a
inner join (select invid,sum([ciscost]) as sumCiscost from #T group by invid)as b on a.invid=b.invid group by a.invid,b.sumCiscost')go/*
invid 材料成本 加工成本 人工费用 委外加工
1233 18.18% 36.36% 18.18% 27.27%
1255 100.0% 0.00% 0.00% 0.00%
*/
insert into #t1
select 1233, N'材料成本', 20, 0
union all
select 1233, N'加工成本', 40, 0
union all
select 1233, N'委外加工', 30, 0
union all
select 1233, N'人工费用', 20, 0
union all
select 1255, N'材料成本', 20, 0select #t1.id,#t1.name,#t1.cost,#t1.cost*100/t.gt [per%] from #t1
left join
(select id,SUM(cost) as gt from #t1
group by id) t
on #t1.id = t.id/*
id name cost per%
1233 材料成本 20 18
1233 加工成本 40 36
1233 委外加工 30 27
1233 人工费用 20 18
1255 材料成本 20 100
*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([invid] int,[csitemname] nvarchar(4),[ciscost] int,[precents] int)
Insert #T
select 1233,N'材料成本',20,0 union all
select 1233,N'加工成本',40,0 union all
select 1233,N'委外加工',30,0 union all
select 1233,N'人工费用',20,0 union all
select 1255,N'材料成本',20,0
Go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+N','+quotename(csitemname)+N'=str(sum(case when csitemname=N'+quotename(csitemname,'''')+N' then [ciscost] else 0 end)*1.0/b.sumCiscost,5,2)'from #T group by csitemname
--顯示生成語句
print N'select a.invid'+@s+N' from #T as a
inner join (select invid,sum([ciscost]) as sumCiscost from #T group by invid)as b on a.invid=b.invid group by a.invid,b.sumCiscost'exec(N'select a.invid'+@s+N' from #T as a
inner join (select invid,sum([ciscost]) as sumCiscost from #T group by invid)as b on a.invid=b.invid group by a.invid,b.sumCiscost')go/*
invid 材料成本 加工成本 人工费用 委外加工
1233 0.18 0.36 0.18 0.27
1255 1.00 0.00 0.00 0.00
*/