How can I pivot a view into a join so I get multiple query rows as columns in a single row? I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data into my view.

What I am trying to achieve is to join this view to my main vehicle table so that I can select the various properties as columns in a single row.

My view:

Vehicle_ID  Property     Value
1           Doors        2
1           Engine Size  2 liter
1           Tire Size    50cm
1           Color        Blue
2           Doors        4
2           Engine Size  3 liter


And my main vehicle table:

Vehicle_ID  Description  Date_Bought  Owner_ID
1           BMW M3 Sport 2000/12/23   1
2           Audi A5 3XL  2015/03/19   1


So what I would like to get out is something like

Vehicle ID  Description   Date_Bought   Doors   [Engine Size]  [Tire Size]  [Color]
1           BMW M3 Sport  2000/12/23    2       2 liter        50cm         Blue


My overall SQL knowledge is fairly limited, I'm pretty good with JOINS and stuff, but the logic of the PIVOT command and its syntax completely escape me.
Using the PIVOT clause, you can achieve the result like this:

SELECT
  Vehicle_ID,
  Doors,
  [Engine Size],
  [Tire Size],
  [Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
;


Now, to join the results to your main vehicle table, you can use the above as a derived table (same as you probably did with the solution you found yourself) – or you can join the main table directly to the results of the PIVOT:

SELECT
  m.Vehicle_ID,
  m.Description,
  m.DateBought,
  p.Doors,
  p.[Engine Size],
  p.[Tire Size],
  p.[Color]
FROM
  dbo.YourView
  PIVOT
  (
    MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color])
  ) AS p
  INNER JOIN dbo.MainVehicleTable AS m ON p.Vehicle_ID = m.Vehicle_ID
;


The above assumes that your view has only the three columns mentioned in your question. If there are more (e.g. time of recording/storing the property or something else), then you will probably need to replace the plain dbo.YourView reference with a derived table where you are pulling only those three columns:

...
FROM
  (
    SELECT
      Vehicle_ID,
      Property,
      Value
    FROM
      dbo.YourView
  ) AS s
  PIVOT
  ...