1

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.

0

3 Answers 3

5

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
  ...
1
  • How to write the same using Dynamic Query?
    – Rohan Rao
    Feb 6, 2020 at 8:45
1

Answering my own question because I found an answer on StackOverflow that was immensely helpful:

https://stackoverflow.com/a/1343174/123729

Using the logic in the question above, the query that works for me doesn't need a PIVOT operation, just a GROUP BY and MIN (or MAX) with a CASE statement, like so:

Select Vehicle_ID,
    Min(Case Property When 'Doors' Then Value End) Doors,
    Min(Case Property When 'Tire Size' Then Value End) [Tire Size],
    Min(Case Property When 'Color' Then Value End) Color
From table
Group By Vehicle_ID

Which I was then able to JOIN on the Vehicle ID.

I hope I haven't screwed up in the faux-SQL above, and that this might help someone.

1

The any Sql DBMS fast runnig pivot is just (MS SQL, you may need replace [] with "" or something for other Sql DBMS)

 select Vehicle_ID 
   ,max(case Property when 'Doors' then Value end) as [Doors]
   ,max(case Property when 'Engine Size' then Value end) as [Engine Size]
   --... 
 from theView
 group by Vehicle_ID

Which MS SQL abbreviates to

select Vehicle_ID -- not pivoted, group by columns 
  ,[Doors], [Engine Size] --  == values from pivoted col surrounded with [], quotes stripped
from theView
PIVOT
(
max(value) -- the same aggregation function and the same pivoted column 
FOR Property IN ([Doors], [Engine Size]) -- values as above once again
) AS PivotTable;

Note the first version allows for multiple columns to be pivoted at one step. MS SQL PIVOT syntax requires multiple steps for this.

Not the answer you're looking for? Browse other questions tagged or ask your own question.