Returning Field names as part of a SQL Query

Question

I need to write a Sql Satement 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 origional data, then pass the result onwards for exporting.

Below my attempt: I have a Sub-Query Callled "A", wich 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

I've never seen the index[0] in the select statement. Are you getting an error when you run the above query? I would think it would throw a syntax error. — SS_DBA, Oct 20 '16 at 12:28
I am getting an error. Thats the part i need help on - getting the right syntax for retrieving the fieldname of the subquery. — Stefan, Oct 20 '16 at 12:32
Does it necessarily have to be in one query? Is that a strict requirement? Eg from your presentation layer (C#, Java, ...) you could analyze the resultset and have the first row contain the column names you found in that analysis. — TT., Oct 20 '16 at 12:46
I could analyze the result-set in C# (more overhead and design issues here), but if i can get the info in one result-set, straight from the db source, with no db-cursors, it would be great.... — Stefan, Oct 20 '16 at 12:57
From experience, I know what you are proposing is not the way to go. The results of your query have specific types (eg, INT, DATETIME, NVARCHAR(16), FLOAT, DECIMAL...). If you would make a query to add the column names to the results, you would have to CAST/CONVERT each column of the original query to some sort of (N)VARCHAR(XXX). Also, each environment I know of (like C#) allows for very easy inspection of the resultset to get the column names associated with each result column. You will have an easier time taking my advise then creating a query that adds the column names as the first row. — TT., Oct 20 '16 at 13:03
Most helpful comment in helping me structure a solution for my situation. Appreciated! — Stefan, Oct 20 '16 at 13:41

iamdave · Accepted Answer · 2016-10-20T13:07:00.303

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       |

Returning Field names as part of a SQL Query

1 Answers1