Pure SQL way to transform value to column name

Question

I've been interested by this question, and the answer seems great.

Nevertheless, as SQL is Turing complete, I would be very curious to see a solution using only pure SQL. I've never used CTE, I guess this is mandatory here.

Answer 1

I believe there is no pure way of achieving so other than dynamic queries. CTE is a good way to structure your query better and refactor it into reusable blocks but again it's not dynamic in that sense!

Any approach which involves passing a string list of columns or WHERE clause to be parsed and executed is not supported directly in Sql server and therefore needs to be executed using SP_EXECUTESQL stored procedure.

The situations mandating dynamic query are very rare, though. If you're extensively using this approach then you gotta have to revise your queries, because most of the queries can be written in normal Sql.

That's my 2 cents on this.