SQL 选择列名作为值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1252580/
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
SQL Selecting column names as values
提问by theringostarrs
I have a table of data with column names:
我有一个带有列名的数据表：
period, Truck, Car, Boat
Where the Columns contain numeric values, and the period column is an identity column having 1 to 48, so there are 48 rows.
其中 Columns 包含数值，而 period 列是具有 1 到 48 的标识列，因此有 48 行。
I would like to massage this table into a format where I have a Name column and a value column as well as the period column eg.
我想将此表转换为一种格式，其中我有一个名称列和一个值列以及句点列，例如。
period, NameOfVehicle, Value
I want to create a view of the orignal table to do this? How can I select the column names and place them and the correct value from that column into the NameOfVehicle and Value columns?
我想创建原始表的视图来执行此操作？如何选择列名并将它们和该列中的正确值放入 NameOfVehicle 和 Value 列中？
回答by cletus
If you have fixed columns you can always do this:
如果您有固定的列，您可以随时执行以下操作：
SELECT period, 'Truck' AS NameOfVehicle, Truck AS Value FROM vehicle
UNION ALL
SELECT period, 'Car', Car FROM vehicle
UNION ALL
SELECT period, 'Boat', Boat FROM vehicle
If the columns are dynamic or unknown then it's a bit harder and will rely on vendor-specific functionality to query the schema and a dynamic SQL statement.
如果列是动态的或未知的，那么它会有点困难，并且将依赖于供应商特定的功能来查询架构和动态 SQL 语句。
回答by Steve Kass
While cletus's query is correct, it's worth pointing out that you can use the keyword UNPIVOT in SQL Server 2005 and later to do almost the same thing:
虽然 cletus 的查询是正确的，但值得指出的是，您可以在 SQL Server 2005 及更高版本中使用关键字 UNPIVOT 来做几乎相同的事情：
select
period, nameOfVehicle, value
from T unpivot (
value for nameOfVehicle in ([Car],[Truck],[Boat])
) as U;
The difference between UNPIVOT and cletus's solution is that UNPIVOT will not include rows for which [value] IS NULL. If you need the NULLs, you'll have to be somewhat sneaky and use a value that can never occur in the table (here I use an empty string):
UNPIVOT 和 cletus 的解决方案之间的区别在于 UNPIVOT 将不包括 [value] 为 NULL 的行。如果您需要 NULL，则必须有点偷偷摸摸，并使用表中永远不会出现的值（这里我使用空字符串）：
with X(period,Car,Truck,Boat) as (
select period,coalesce(Car,''),coalesce(Truck,''),coalesce(Boat,'')
from T
)
select
period, nameOfVehicle, case when value = '' then null else value end as value
from X unpivot (
value for nameOfVehicle in ([Car],[Truck],[Boat])
) as U;
Depending on the columns you'll keep after unpivoting, this may be another option (this solution will retain the NULL values):
根据逆透视后您将保留的列，这可能是另一种选择（此解决方案将保留 NULL 值）：
select
period,
nameOfVehicle,
max(case nameOfVehicle
when 'Truck' then Truck
when 'Car' then Car
when 'Boat' then Boat end) as value
from T cross join (
values ('Truck'),('Car'),('Boat')
) as Columns(nameOfVehicle)
group by period, nameOfVehicle;
