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?