dynamically change column name when displaying

SQL Apprentice Question
have a table, after i query the table for displaying, several column names
will be changed based on the condition, but I don't want change the acutal
table column names, it will only be changed when displaying.
I tried CTE and virtual table with column aliases, but they doesn't work,
they require you know the column name first, i CANNOT pass @columnName to
them.

I can certainly create a temp table and copy all the data from the orginal
table, and then based on the condition to rename the column name of the temp
table, then display it, but that is not efficient.


Celko Answers
You have missed the ENTIRE CONCEPT OF A TIERED ARCHITECTURE!!

You NEVER query a table for display; you query a table for **data**
and the front end does their display. You are writing 1950's file
systems in SQL!!