I am of the view that unfortunately this can not be solved dynamically per se, for the columns that would be "dynamic" can only be referenced in a static context.
This example serves to firmly reinforce why correct database design methods should be adhered, to avoid issues exactly like this. Had the table been developed to 1NF, for this form would have sufficed to solve the problem you describe, the solution could have been delivered with a simplistic cross-tab query or variation thereof. In particular, the support for CASE expressions in SQL Server and MS Access allows for powerful queries of this kind to be developed quickly and to perform very efficiently.
With the table design that you have, however, I can only agree with your reluctant proposal to develop each column to row transformation manually and to amalgamate the results with the UNION operator.
You may want to consider creating a view of this result set to ease in the development of future queries that need to either return a view of this result set, or use it in the construction of another query.
The following comment is not directed directly at the original poster.
It is rather alarming that today, with the continuous supply of examples of ill-designed database schemas that inadvertently result in the development of unnecessarily complex and impractical solutions to query the data, that the importance of understanding the theory and principles of database design and querying principles is all too often undervalued.
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.