具有多列的枢轴和和

Question

我有一个查询，它创建以下数据：

ID          LegacyID                type1 type2 date        value
1500000001  10070242801000000217    DF    RN    2018-05-24  -3.33000000
1500000002  10078387921000000217    PP    IN    2018-05-26  -11.00000000
1500000002  10078387921000000217    PP    RN    2018-05-26  -100.00000000
1500000003  10080625841000000217    DF    RN    2018-05-23  -3.33000000
1500000003  10080625841000000217    DF    RN    2018-05-23  99.000000

我试图将数据转到类型字段上，在按ID、LegacyID和date字段分组的同时，对值进行求和。

创建如下：

id              legacyid                    date        DFRN          PPIN          PPRN
1500000001      10070242801000000217        2018-05-24  -3.33000000   0             0
1500000002      10078387921000000217        2018-05-26  0            -11.00000000   -100.00000000
1500000003      10080625841000000217        2018-05-23  95.67000000   0             0

注意，我正在尝试将类型字段连接到它们的唯一组合中。

我尝试过几个支点的突变，但我似乎做不到，我最近的尝试(不起作用)如下所示：

    select
        id,
        legacyid,
        [date],
        [DF]+[RN] as DFRN,
        [CR]+[CR] as CRCR,
        [CR]+[MR] as CRMR,
        [DF]+[DS] as DFDS,
        [DF]+[MR] as DFMR,
        [PP]+[DS] as PPDS,
        [PP]+[IN] as PPIN,
        [PP]+[RN] as PPRN,
    from (
    select
        tl.[id],
        r.[LegacyId],
        a.type1,
        bt.type2,   
        [Date],
        gl.TotalAmountUSD as [value]
    from
       ----long join
    where
        gh.IsActive = 1                             
    group by 
        tl.id,
        r.LegacyId,
        a.type1,
        bt.type2,
        [date])
    ) as bb
    pivot
    (
        sum(value)
        for rentalid in (
        [DF]+[RN] as DFRN,
        [CR]+[CR] as CRCR,
        [CR]+[MR] as CRMR,
        [DF]+[DS] as DFDS,
        [DF]+[MR] as DFMR,
        [PP]+[DS] as PPDS,
        [PP]+[IN] as PPIN,
        [PP]+[RN] as PPRN)
    ) as p

一个人将如何转向这一点？

Answer 1

只需使用条件聚合：

with t as (
      . . .
     )
select ID, LegacyID, date,
       sum(case when type1 = 'DF' and type2 = 'RN' then value end) as DFRN,
       sum(case when type1 = 'PP' and type2 = 'IN' then value end) as PPIN,
       sum(case when type1 = 'PP' and type2 = 'RN' then value end) as PPRN
from t
group by ID, LegacyID, date ;