CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Christopher Turnbull
Christopher Turnbull

Reputation: 1003

Pivot aggregates in SQL

I'm trying to find a way to pivot the table below (I guess you would say it's in "long" format) into the ("wider") format where all the columns are essentially explicitly Boolean. I hope this simple example gets across what I'm trying to do.

Note there is about 74 people. (so the output table will have 223 columns, 1 + 74 x 3 )

I can't figure out an easy way to do it other than horribly with a huge number of left joins along "Town" by statements like

... left join(
select 
   town, 
   case where person = 'Richard' then 1 else 0 end as "Richard"
   Fee as "Richard Fee"
from services 
where person = 'Richard'
left join...

can some smart person suggest a way to do this using PIVOT functions in SQL? I am using Snowflake (and dbt so I can get some jinja into play if really necessary to loop through all the people).

Input:

enter image description here

Desired output:

enter image description here

ps. I know this is a ridiculous SQL ask, but this is the "output the client wants" so I have this undesirable task to fulfil.

Upvotes: 1

Views: 146

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

If persons are known in advance then you could use conditional aggregation:

SELECT town,
       MAX(CASE WHEN person = 'Richard' THEN 1 ELSE 0 END)  AS "Richard",
       MAX(CASE WHEN person = 'Richard' THEN Fee END)       AS "Richard Fee",
       MAX(CASE WHEN person = 'Richard' THEN Service END)   AS "Richard Service",
       MAX(CASE WHEN person = 'Caitlin' THEN 1 ELSE 0 END)  AS "Caitlin",
       ...
FROM services 
GROUP BY town;

Upvotes: 1

Related Questions