How to add table column headings to sql select sta

问题:

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?

回答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

回答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.

回答3:

The UNION All is the solution except it should be pointed out that:

1. To add a header to a non-char column will require converting the column in the first part of the query.
2. If the converted column is used as part of the sort order then the field reference will have to be to the name of the column in the query, not the table

example:

Select Convert(varchar(25), b.integerfiled) AS [Converted Field]...
... Order by [Converted Field]