SQL Server中多个列上的聚合函数

I have the following data in a #temp table:

我在#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

I want to do following operation i.e

我想做以下操作。

One Row will be addition on two columns i.e ERA + ESHIFTALLOW Other Row will be subtraction & addition on three columns i.e NETPAY - ERA + ESHIFTALLOW I had tried using case statement in SQL Server.

在两列i上加一行。e ERA + ESHIFTALLOW其他行将是减法&加在3列i。我尝试过在SQL Server中使用case语句。

Following is the output required

下面是所需的输出

where Field1= ERA + ESHIFTALLOW & Filed2=NETPAY - ERA + ESHIFTALLOW

where 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

I had tried using SQL SERVER Case Statement but not getting proper output SQL Query : Aggregate option in SQL Server CASE statement

我尝试过使用SQL SERVER Case语句，但是没有在SQL SERVER Case语句中获得正确的输出SQL Query:聚合选项

3 个解决方案

#1

I see at least 2 methods to get those results. A group by or a pivot

我至少看到了两种方法来得到这些结果。一组或一个支点

In the example below the 2 methods are shown.

下面的示例显示了这两种方法。

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
;

Note that SUM(VALUE) was used instead of MAX(VALUE). In this case it will yield the same results. It's just a choice really.

注意，使用SUM(VALUE)而不是MAX(VALUE)。在这种情况下，它将产生相同的结果。这只是一个选择。

#2

Building heavily on LukStorms' answer, you can use a PIVOT and an UNPIVOT to get the results you want:

在LukStorms的回答的基础上，你可以用一个枢轴和一个枢轴来获得你想要的结果:

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);


SELECT Id, Code, Fname, CompanyId, FieldName, Value 
FROM (
    SELECT Id, Code, Fname, CompanyId, 
    ERA +  ESHIFTALLOW AS Field1,
    NETPAY - ERA +  ESHIFTALLOW AS Field2
    FROM (
        SELECT * 
        FROM #Temp
    ) AS s
    PIVOT ( 
        SUM(Value) 
        FOR FieldName IN (ERA, ESHIFTALLOW, NETPAY)
    ) AS p
) AS r
UNPIVOT (
    Value 
    FOR FieldName IN (Field1, Field2)
) AS u
;

#3

I have no idea whether this solution is anywhere near the most efficient, but it should work:

我不知道这个解决方案是否最有效，但它应该是有效的:

SELECT 
  BASE.*,
  ERA.Value AS ERA,
  ESALLOW.Value AS ESHIFTALLOW,
  ERA.Value + ESALLOW.Value AS Field1,
  etc...
FROM (
   SELECT DISTINCT Id, code, Fname, CompanyId
   FROM #TEMP ) BASE
LEFT OUTER JOIN (
   SELECT Id, Value
   FROM #TEMP
   WHERE FieldName = 'ERA' ) ERA
ON BASE.Id = ERA.Id
LEFT OUTER JOIN (
   SELECT Id, Value
   FROM #TEMP
   WHERE FieldName = 'ESHIFTALLOW' ) ESALLOW
ON BASE.Id = ESALLOW.Id

This gives you a simple table that has every type of value in a separate column, instead of in separate rows. This makes calculations possible.

这为您提供了一个简单的表，该表在单独的列中包含所有类型的值，而不是在单独的行中。这使得计算成为可能。

#1