Geek Answers Handbook

SQL Choosing column names as values

I have a data table with column names:

period, Truck, Car, Boat

If the columns contain numeric values ​​and the period column contains columns 1 through 48, there are therefore 48 rows.

I would like to massage this table in a format where I have a Name column and a value column, as well as a period column, for example.

period, NameOfVehicle, Value

I want to create an orignal table view for this? How can I select column names and put them and the correct value from this column in the NameOfVehicle and Value columns?

+5
source share
2 answers

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.

+10

cletus , , UNPIVOT SQL Server 2005 , :

  select
    period, nameOfVehicle, value
  from T unpivot (
    value for nameOfVehicle in ([Car],[Truck],[Boat])
  ) as U;

UNPIVOT cletus , UNPIVOT , [] 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;

, , ( 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;
+2

More articles:


All Articles