sacag sacag - 5 days ago 5
SQL Question

Using t-sql how to convert the below example of rows into columns

Entity Description Units AssetId
Vehicle 100 A Distance 8.48 2
Vehicle 100 A Fuel 11 2
Vehicle 100 A Parking 9 2
Vehicle 100 A Tolls 10 2
Vehicle 1 Distance 8.48 5
Vehicle 1 Fuel 8 5
Vehicle 1 Parking 6 5
Vehicle 1 Tolls 7 5


Transform above dataset to:

Vehicle Description Distance Fuel Parking Tolls AssetId
Vehicle 100 A 8.48 11 9 10 2
Vehicle 1 8.48 8 6 7 5

Answer

A simple conditional aggregation may do the trick.

Select [Vehicle Description] = Entity          
      ,Distance  = max(case when Description='Distance'  then Units else null end)
      ,Fuel      = max(case when Description='Fuel'      then Units else null end)
      ,Fuel      = max(case when Description='Parking'   then Units else null end)
      ,Tolls     = max(case when Description='Tolls'     then Units else null end)
      ,AssetId   = max(AssetID)
  From YourTable
  Group By Entity          
Comments