我在#temp表中具有以下数据:
Id code Fname CompanyId FieldName Value
----------------------------------------------------------------
465 00133 JENN WILSON 1 ERA 1573
465 00133 JENN WILSON 1 ESHIFTALLOW 3658
465 00133 JENN WILSON 1 NETPAY 51560
我想做以下操作,即
一行将在两列上加法,即ERA + ESHIFTALLOW
另一行将在三列上减法加法,即NETPAY - ERA + ESHIFTALLOW
我曾尝试在SQL Server中使用case语句。
以下是所需的输出
哪里Field1= ERA + ESHIFTALLOW
&Filed2=NETPAY - ERA + ESHIFTALLOW
Id code Fname CompanyId FieldName Value
----------------------------------------------------------------
465 00133 JENN WILSON 1 Field1 5231
465 00133 JENN WILSON 1 Filed2 46329
我曾尝试使用SQL SERVER Case语句,但未获得正确的输出SQL Server CASE语句中的SQL Query:Aggregate选项
我看到至少有两种方法可以得到这些结果。分组或枢纽
在下面的示例中,显示了2种方法。
CREATE TABLE #Temp (Id INT, code VARCHAR(5), Fname VARCHAR(20), CompanyId INT, FieldName VARCHAR(20), Value INT);
insert into #Temp (Id, code, Fname, CompanyId, FieldName, Value)
values
(465,00133,'JENN WILSON',1,'ERA',1573),
(465,00133,'JENN WILSON',1,'ESHIFTALLOW',3658),
(465,00133,'JENN WILSON',1,'NETPAY',51560);
with Q AS (
SELECT Id, code, Fname, CompanyId,
sum(case when FieldName = 'ERA' then Value end) as ERA,
sum(case when FieldName = 'ESHIFTALLOW' then Value end) as ESHIFTALLOW,
sum(case when FieldName = 'NETPAY' then Value end) as NETPAY
from #Temp
group by Id, code, Fname, CompanyId
)
select Id, code, Fname, CompanyId, 'Field1' as FieldName, (ERA + ESHIFTALLOW) as Value from Q
union all
select Id, code, Fname, CompanyId, 'Field2', (NETPAY - ERA + ESHIFTALLOW) from Q
;
with Q AS (
SELECT Id, code, Fname, CompanyId,
(ERA + ESHIFTALLOW) as Field1,
(NETPAY - ERA + ESHIFTALLOW) as Field2
FROM (SELECT * FROM #Temp) s
PIVOT ( SUM(VALUE) FOR FieldName IN (ERA, ESHIFTALLOW, NETPAY)) p
)
select Id, code, Fname, CompanyId, 'Field1' as FieldName, Field1 as Value from Q
union all
select Id, code, Fname, CompanyId, 'Field2', Field2 from Q
;
请注意,使用的是SUM(VALUE)而不是MAX(VALUE)。在这种情况下,它将产生相同的结果。这只是一个选择。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句