将多个列的聚合值作为键，将列转为行。

我怎么能把这个。

转化成这个

我运行的初始查询如下

    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

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

    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 ')'.

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

解决方案：

这是你想要的吗？

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