I have the following query that gives me aggregated data that I would like to pivot:
select provider, title, hd_or_sd, SUM(customer_price), count(distinct title)
from `100` group by provider, title, hd_or_sd with rollup
I would like to pivot on the sd_or_hd
column, so that my result looks like:
hd sd total
provider title rev. count rev. count rev. count
DISTRIBBER 16.99 1 16.99 1
DISTRIBBER finding Joe 16.99 1 16.99 1
How would I do this query? Also, I have noticed that the Rollup is not perfect -- for example, with Electric entertainment, it shows 5 titles but has the hd_or_sd value "HD", even though several of those titles are SD. Why does this occur?
Here is a sample of the data: http://sqlfiddle.com/#!9/a9b5d9/1. And here is what the final result should be (minus the strange number formatting) --
Note that I'd also like to accomplish this without using CASE
statements as the pivoted column could potentially have many different values.