将列转置为具有多个列的聚合值作为键的行

Question

我如何将其转换为：

​

​

如下所示：

​

​

我运行的初始查询如下：

    select * from

(select a.curr_djf as date, 
replace ((replace (a.channel_group, 'iSelect', 'Aggregators')), 'Other Aggregator', 'Aggregators') channel,
sum(a.pols) pols, sum(a.pmplseu) pseu

from [DW_PHI_BASE].[dbo].[phdr_daily] a
and a.curr_djf between '2020-04-20' and dateadd(day,-1, cast(getdate() as date))
group by a.channel_group, a.curr_djf) d

我尝试过使用轴心和交叉应用，但失败了。我的最后一次尝试是使用交叉应用：

    select
    d.*
    from
    (select a.curr_djf as date, 
    replace ((replace (a.channel_group, 'iSelect', 'Aggregators')), 'Other Aggregator', 'Aggregators') channel,
    sum(a.pols) pols, sum(a.pmplseu) pseu

    from [DW_PHI_BASE].[dbo].[phdr_daily] a
    where
a.curr_djf between '2020-04-20' and dateadd(day,-1, cast(getdate() as date))
    group by a.channel_group, a.curr_djf) d

    cross apply (values
    ('pols', pols),
    ('PSEUs', pseu),
    ) x(channel, value)

我得到了下面的错误：

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

请帮助我了解如何将表格修复为正确的格式。

Answer 1

这就是你要找的东西吗？

Select [Date], channel, measure, [value]
FROM dbo.phdr_daily
UNPIVOT
([value]
FOR measure
IN([pols], [pseu])
)
AS Unpivoted_Results

Answer 2

您的版本应该没问题，尽管您确实想选择正确的列：

select d.date, d.channel, x.*
from (select d.curr_djf as date, 
            replace(replace(a.channel_group, 'iSelect', 'Aggregators'), 'Other Aggregator', 'Aggregators') as channel,
            sum(d.pols) as pols, sum(d.pmplseu) as pseu
      from [DW_PHI_BASE].[dbo].[phdr_daily] d
      where a.curr_djf between '2020-04-20' and dateadd(day, -1, cast(getdate() as date))
      group by a.channel_group, a.curr_djf
    ) d cross apply
    (values ('pols', d.pols),
            ('PSEUs', d.pseu),
    ) x(channel, value);

我怀疑您真的希望通过channel而不是channel_group进行聚合，但这是另一回事。