SQL Selecting column names as values

Question

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.

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?

Answer 1

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.

Answer 2

Hi jimioh,

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:

  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):

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):

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;