How to add table column headings to sql select statement

Question

I have a SQL select statement like this:

select FirstName, LastName, Age from People

This will return me something like a table:

Peter  Smith    34
John   Walker   46
Pat    Benetar  57

What I want is to insert the column headings into the first row like:

First Name  Last Name  Age
=========== ========== ====
Peter       Smith      34
John        Walker     46
Pat         Benetar    57

Can someone suggest how this could be achieved?

Could you maybe create a temporary table with the headings and append the data one to this?

Answer 1

Neither of the answers above will work, unless all your names come after "first" in sort order.

Select FirstName, LastName
from (
    select Sorter = 1, FirstName, LastName from People
    union all
    select 0, 'FirstName', 'LastName') X
order by Sorter, FirstName   -- or whatever ordering you need

If you want to do this to all non-varchar columns as well, the CONS are (at least):

1. ALL your data will become VARCHAR. If you use Visual Studio for example, you will NO LONGER be able to recognize or use date values. Or int values. Or any other for that matter.
2. You need to explicitly provide a format to datetime values like DOB. DOB values in Varchar in the format dd-mm-yyyy (if that is what you choose to turn them into) won't sort properly.

The SQL to achieve this, however not-recommended, is

Select FirstName, LastName, Age, DOB
from (
    select Sorter = 1,
        Convert(Varchar(max), FirstName) as FirstName,
        Convert(Varchar(max), LastName)  as LastName,
        Convert(Varchar(max), Age)       as Age,
        Convert(Varchar(max), DOB, 126)  as DOB
    from People
    union all
    select 0, 'FirstName', 'LastName', 'Age', 'DOB') X
order by Sorter, FirstName   -- or whatever ordering you need

Answer 2

The lightest-weight way to do this is probably to do a UNION:

SELECT 'FirstName' AS FirstName, 'LastName' AS LastName
UNION ALL
SELECT FirstName, LastName 
FROM People

No need to create temporary tables.