SQL query with dynamic unpivot+pivot for cross product
There is a more general way to do it, but it involves a dynamic query..... which is messy, and may not be better than what you have. Any time you involve a pivot, you are going in to the 'ugly' territory.

First though, some other comments:


you do not include the Rawdata CTE with your question, and it has some small issues I can see in SEDE
You select your data out as formatted strings, and you should only do the formatting as the last step, if possible.


Other than that, I found your query relatively easy to read, and since I understand the SEDE dataset quite well, I could follow the logic nicely.

Basically, your query is good, and I would 'accept it' in a review.

Ugly things of note though are:


max(Progress) is doing character-based max on a single-value set
the order by does a convert-to-float of a character value


I would like those things to be commented on, but, since the data volumes for those operations are small, I think it is OK, given the circumstances.

Out of interest, I processed your query using a more general syntax, that avoids the unpivot, and makes the pivot dynamic. As you will see, it is just as ugly, if not worse.


it does not do the order-by
it does not format the values as string at all.


See it for yourself

Note the multiple temp tables, and the variables for the pivot, and the query. Ugly, but it may give you some ideas.