CODEWITHSUNDEEP
sqlpivot
Sathish
Sathish

Reputation: 71

SQL Pivot query with multiple column grouping

I have a table data as follows

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

Now, i would like to have a result by pivoting Category and Day, as follows.

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

I have tried few queries but could not achieve grouping 2 columns, day and category. Sorry for data format issue. Please help.

Here is my query

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

Thank you, Sathish

Upvotes: 5

Views: 3526

Answers (2)

sagi
sagi

Reputation: 40481

For ANSI-Sql you can use conditional aggregation :

SELECT t.group , 
       MAX(CASE WHEN t.day = 'Sun' AND t.category = 'music' AND THEN t.appcount) as Sun-Music,
       MAX(CASE WHEN t.day = 'Sun' AND t.category = 'radio' AND THEN t.appcount) as Sun-Radio,
       MAX(CASE WHEN t.day = 'Mon' AND t.category = 'music' AND THEN t.appcount) as Mon-Music,
       MAX(CASE WHEN t.day = 'Mon' AND t.category = 'radio' AND THEN t.appcount) as Mon-Radio,
       ........
FROM YourTable t
GROUP BY t.group

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I find it simpler to just use conditional aggregation for many pivot queries:

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;

Upvotes: 2

Related Questions