sql - 对具有多个总和的多列进行透视

Question

我有以下示例数据:

+------------+--------+--------+----------+----------+
|    Type    | Total1 | Total2 | Account1 | Account2 |
+------------+--------+--------+----------+----------+
| Adjustment |  -2.14 |   2.14 |     1220 |     4110 |
| Adjustment |   0.21 |  -0.21 |     1220 |     4110 |
| Adjustment |  -6.43 |   6.43 |     1220 |     1220 |
+------------+--------+--------+----------+----------+

我想要做的是对与 Total1 列相关的 Account1 列以及与 Total2 列相关的 Account2 表进行 PIVOT/SUM。

但是，在进行数据透视时，我需要按 Account1 和 Account2 的组合进行透视，并按该帐户代码的相关总计列求和，因此使用此示例数据，我最终得到以下结果:

+------------+-------+------+
|    Type    | 1220  | 4110 |
+------------+-------+------+
| Adjustment | -1.93 | 1.93 |
+------------+-------+------+

到目前为止，我的两次尝试都包含此内容，但还没有完全实现。有人可以告诉我缺少什么吗？

select 
    Type,
    sum([1220]) as [1220],
    sum([4110]) as [4110]
from #temp
    pivot
    (
      sum(Total1)
      for Account1 in ([1220],[4110])
    ) p
group by Type

select
    Type,
    sum(case When Account1 = '1220' Then Total1 WHEN Account2 = '1220' Then Total2 end) as [1220],
    sum(case When Account1 = '4110' Then Total1 WHEN Account2 = '4110' Then Total2 end) as [4110]
from #temp
group by Type

示例数据:

CREATE TABLE #temp
(
    Type varchar(50),
    Total1 money,
    Total2 money,
    Account1 int,
    Account2 int
)

insert into #temp (Type, Total1, Total2, Account1, Account2)
    select 'Adjustment', '-2.14', '2.14', '1220', '4110'
    union all
    select 'Adjustment', '0.21', '-0.21', '1220', '4110'
    union all
    select 'Adjustment', '-6.43', '6.43', '1220', '1220'

Answer 1

我们可以尝试通过联合方法来处理这个问题:

WITH cte AS (
    SELECT [Type], Total1 AS Total, Account1 AS Account FROM #temp
    UNION ALL
    SELECT [Type], Total2, Account2 FROM #Temp
)

SELECT
    [Type],
    SUM(CASE WHEN Account = '1220' THEN Total ELSE 0 END) AS [1220],
    SUM(CASE WHEN Account = '4110' THEN Total ELSE 0 END) AS [4110]
FROM cte
GROUP BY
    Type;

Demo

此处的基本策略是将所有帐户和总计放入两个单独的列中，然后在这些单个列上进行聚合/透视。这解决了您想要跨多个列聚合数据的问题。最好的长期解决办法可能是将您的数据结构更改为仅包含帐户和总计的单个列。