Column names of a CTE in SQL Server

Question

I know it is possible to SELECT, from sys.columns and from tempdb.sys.columns the names of the columns of a specific table.

Can the same be done from a CTE?

with SampleCTE as (
 Select 
   'Tom' as Name
  ,'Bombadill' as Surname
  ,99999 as Age
  ,'Withywindle' as Address
)

is there any way to know that the columns of this CTE are Name,Surname,Age and Address, without resorting to dumping the CTE result to a temporary table and reading the columns from there?

Thanks!

Answer 1

Here is a "dynamic" approach without actually using Dynamic SQL.

Unpivot (dynamic or not) would be more performant

Example

with SampleCTE as (
 Select 
   'Tom' as Name
  ,'Bombadill' as Surname
  ,99999 as Age
  ,'Withywindle' as Address
) 
Select C.*
 From SampleCTE A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')
             ) C

Returns

Item    Value
Name    Tom
Surname Bombadill
Age     99999
Address Withywindle