SQL 选择列名作为值 [英] SQL Selecting column names as values

问题描述

我有一个带有列名的数据表:

I have a table of data with column names:

period, Truck, Car, Boat

其中 Columns 包含数字值，而 period 列是具有 1 到 48 的标识列，因此有 48 行.

Where the Columns contain numeric values, and the period column is an identity column having 1 to 48, so there are 48 rows.

我想将此表调整为一种格式，其中我有一个名称列和一个值列以及句点列，例如.

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

我想创建原始表的视图来执行此操作?如何选择列名称并将它们和该列中的正确值放入 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?

推荐答案

如果你有固定的列，你总是可以这样做:

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

如果列是动态的或未知的，那么它会有点困难，并且将依赖于供应商特定的功能来查询架构和动态 SQL 语句.

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 选择列名作为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！