具有多列分组的 SQL Pivot 查询

Question

我有一个表数据如下

day group   category    appcount
-----------------------------------------
Fri F27-28  music   4
Fri F27-28  radio   1
Fri F27-28  show    1
Fri F27-28  video   8
Fri F29-32  music   6
Fri F29-32  radio   2
Fri F29-32  video   22
Fri M22-    music   1
Fri M22-    video   2
Fri M23-26  music   4
Fri M23-26  video   8

现在，我想通过旋转类别和日期来获得结果，如下所示。

Age Group   music-Fri   music-Mon   music-Sun   music-Tue   music-Sat   music-Thu   music-Wed   radio-Fri   radio-Mon   radio-Sun   radio-Tues  radio-Sat   radio-Thu   radio-Wed
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
F27-28  4   16  5   11  17  13  9   1   1   3   2   8   2   
F29-32  6       2   6   4   4   4   2   5       2       3   2
F33-42      2   2               3       1   1               3
M22-    1   15  14  10      4   4                           
M23-26  4   7   5   2   12  14  7                           

我尝试了一些查询，但无法实现对 2 列、日期和类别进行分组。抱歉数据格式问题。请帮忙。

这是我的查询

SELECT *
FROM (
    SELECT 
        [group],
        [day],
        category, 
        appcount as counts 
    FROM monthly_age_apps
) as s
PIVOT
(
    SUM(counts)
    FOR [category] IN (video, radio, show, music)
)AS pvt

谢谢你，萨蒂什

Answer 1

我发现对许多数据透视查询使用条件聚合更简单:

select agegroup,
       sum(case when category = 'music' and day = 'Fri' then appcount else 0 end) as music_fri,
       sum(case when category = 'music' and day = 'Mon' then appcount else 0 end) as music_mon,
       . . .
from 
group by agegroup;