SQL Pivot query with multiple column grouping

Question

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

You should edit the question and show the queries that you tried. — Gordon Linoff
– Gordon Linoff, Commented Sep 8, 2016 at 10:49

Gordon Linoff · Accepted Answer · 2016-09-08 10:49:30Z

2

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;

answered Sep 8, 2016 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sathish Over a year ago

Thanks for your reply. Can we do this dynamically?

Gordon Linoff Over a year ago

Google: "<database name> dynamic pivot". That is a very different question from the one you asked.

sagi · Accepted Answer · 2016-09-08 10:49:49Z

1

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

answered Sep 8, 2016 at 10:49

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Collectives™ on Stack Overflow

SQL Pivot query with multiple column grouping

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related