如何将表列标题添加到sql select语句 [英] How to add table column headings to sql select statement
问题描述
select人名的姓氏,姓氏,年龄
code>
这会返回一些类似于表格的内容:
Peter Smith 34
John Walker 46
Pat Benetar 57
我想要的是将列标题插入第一行,如:
名字姓氏年龄
=========== ========== ====
Peter Smith 34
John Walker 46
Pat Benetar 57
有人可以建议如何实现这个目的吗?
你可能会创建一个带有标题的临时表,并将数据追加到这个表中? 上面的答案都不会起作用,除非你所有的名字都按照第一次的顺序排列。
从$($ b $)中选择名字, b选择分拣机= 1,FirstName,姓氏来自People
union all
选择0,'FirstName','LastName')X
按Sorter,FirstName排序 - 或者任何您需要的订单
如果您也想对所有非varchar列执行此操作,则CONS(至少)是: p>
- 所有数据都将变为VARCHAR。例如,如果您使用Visual Studio,则不会识别或使用日期值。或者int值。或任何其他事情。
- 您需要明确提供日期时间值(如DOB)的格式。以dd-mm-yyyy格式的Varchar中的DOB值(如果这是您选择将其转换为的格式)将无法正确排序。
实现此功能的SQL,不过不推荐是
选择名字,姓氏,年龄,DOB
从(
转换(Varchar(max),LastName)转换(Varchar(max),FirstName)作为姓,
选择Sorter = 1,
作为LastName ,
转换(Varchar(max),Age)作为年龄,
转换(Varchar(max),DOB,126)作为DOB
从People
union全部
选择0,'FirstName','LastName','Age','DOB')X
按Sorter,FirstName或其他命令排序
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?
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):
- 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.
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
这篇关于如何将表列标题添加到sql select语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!