Returning Field names as part of a SQL Query

Question

I need to write a SQL Statement that gets passed any valid SQL subquery, and return the the resultset, WITH HEADERS.

Somehow I need to interrogate the resultset, get the fieldnames and return them as part of a "Union" with the original data, then pass the result onwards for exporting.

Below my attempt: I have a Sub-Query Called "A", which returns a dataset and I need to query it for its fieldnames. ?ordinally maybe?

select A.fields[0].name, A.fields[1].name, A.fields[2].name  from 
(

Select 'xxx1' as [Complaint Mechanism]  , 'xxx2' as [Actual Achievements]
union ALL 
Select 'xxx3' as [Complaint Mechanism]  , 'xxx4' as [Actual Achievements]
union ALL 
Select 'xxx5' as [Complaint Mechanism]  , 'xxx6' as [Actual Achievements]   ) as A

Any pointers would be appreciated (maybe i am just missing the obvious...)

The Resultset should look like the table below:

F1                      F2
---------------------   ---------------------
[Complaint Mechanism]   [Actual Achievements]
xxx1                    xxx2
xxx3                    xxx4
xxx5                    xxx6

Answer 1

If you have a static number of columns, you can put your data into a temp table and then query tempdb.sys.columns to get the column names, which you can then union on top of your data. If you will have a dynamic number of columns, you will need to use dynamic SQL to build your pivot statement but I'll leave that up to you to figure out.

The one caveat here is that all data under your column names will need to be converted to strings:

select 1 a, 2 b
into #a;

select [1] as FirstColumn
        ,[2] as SecondColumn
from (
    select column_id
            ,name
    from tempdb.sys.columns
    where object_id = object_id('tempdb..#a')
    ) d
pivot (max(name)
        for column_id in([1],[2])
        ) pvt

union all

select cast(a as nvarchar(100))
    ,cast(b as nvarchar(100))
from #a;

Query Results:

| FirstColumn | SecondColumn |
|-------------|--------------|
|      a      |      b       |
|      1      |      2       |