sql - SQL Server 中多列的聚合函数

Question

我在#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 查询:Aggregate option in SQL Server CASE statement

Answer 1

我看到至少有两种方法可以获得这些结果。分组依据或枢轴

在下面的示例中显示了 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)。在这种情况下，它将产生相同的结果。这真的只是一个选择。