SQL 如何将表列标题添加到sql select语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4964162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add table column headings to sql select statement
提问by Coder 2
I have a SQL select statement like this:
我有一个像这样的 SQL select 语句：
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?
您能否创建一个带有标题的临时表并将数据附加到该表中？
回答by RichardTheKiwi
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):
如果您还想对所有非 varchar 列执行此操作，则 CONS 是（至少）：
- 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.
- 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.
- 您的所有数据都将变为 VARCHAR。例如，如果您使用 Visual Studio，您将不再能够识别或使用日期值。或 int 值。或者任何其他的。
- 您需要明确地为 DOB 等日期时间值提供格式。Varchar 中的 DOB 值采用 dd-mm-yyyy 格式（如果您选择将它们转换成这种格式）将无法正确排序。
The SQL to achieve this, however not-recommended, is
实现此目的的 SQL，但不推荐，是
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
回答by Dan J
The lightest-weight way to do this is probably to do a UNION:
最轻量级的方法可能是做一个 UNION：
SELECT 'FirstName' AS FirstName, 'LastName' AS LastName
UNION ALL
SELECT FirstName, LastName
FROM People
No need to create temporary tables.
无需创建临时表。
回答by Gerry
The UNION Allis the solution except it should be pointed out that:
这UNION All是解决方案，但应指出的是：
- To add a header to a non-char column will require converting the column in the first part of the query.
- 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]
