SQL query with dynamic unpivot+pivot for cross product
In this Data Explorer query I am trying to do the following:
- For each tag:
Compute sum of answer scores in this tag ("S")
Compute count of answers in this tag ("A")
For each tag class (Bronze, Silver, Gold)
add two columns:- S divided by this class's score goal
- A divided by this class's answer goal
I wanted to do this in the most general way possible, allowing more tag classes/goals to be added later. I came up with this:
-- Predefined tag badge goals
... TagBadges as (
select * from
(values
(1, 'Bronze', 100, 20),
(2, 'Silver', 400, 80),
(3, 'Gold', 1000, 200))
as Badge(Idx, Class, Score, Answers)
),
-- Progress per tag, per badge class
TypeProgress as (
select
RawData.TagName,
format(iif(RawData.Score > TagBadges.Score, 1,
cast(RawData.Score as float)/TagBadges.Score), '#0.#%') as Score,
format(iif(RawData.Answers > TagBadges.Answers, 1,
cast(RawData.Answers as float)/TagBadges.Answers), '#0.#%') as Answers,
TagBadges.Class
from RawData cross join TagBadges
),
-- Combine class & type columns
AllProgress as (
select TagName, Progress, Class+' '+Type as Category
from TypeProgress
unpivot (Progress for Type in (Score, Answers)) p
) ...
But in the end I still had to list all the cases (2 ×ばつ 3 = 6) explicitly:
select *
from AllProgress
pivot (
max(Progress) for Category in
([Bronze Score], [Bronze Answers],
[Silver Score], [Silver Answers],
[Gold Score], [Gold Answers])
) q
Is there a better way of doing this?
lang-sql