Row to Column transformation without Aggregate function in SQL

Question

Folks, Below is the sample table that I have..

Current Table

PropertyAttributeValueID PropertyTypeID PropertyAttributeName PropertyAttributeValue
1000                     3216           Mileage               20.4
1000                     3216           Engine                DIESEL
1000                     3216           Manufacturer          HONDA
1000                     3216           Seat_Capacity         5
1001                     3216           Mileage               19.2
1001                     3216           Engine                PETROL
1001                     3216           Manufacturer          SUZUKI
1001                     3216           Seat_Capacity         4
1002                     3216           Mileage               18.0
1002                     3216           Engine                DIESEL
1002                     3216           Manufacturer          SUZUKI
1002                     3216           Seat_Capacity         4
1003                     3216           Mileage               16.3
1003                     3216           Engine                PETROL
1003                     3216           Manufacturer          HYUNDAI
1003                     3216           Seat_Capacity         5

I need to create this table from the above one

Desired Table

PropertyAttributeValueID Mileage Engine Manufacturer Seat_Capacity
1000                     20.4    DIESEL HONDA        5
1001                     19.2    PETROL SUZUKI       4
1002                     18.0    DIESEL SUZUKI       4
1003                     16.3    PETROL HYUNDAI      5

I thought of using pivot, but As you can see, there's no aggregate function needed, How do I create it?

Answer 1

You could use inner join

select a.PropertyAttributeValueID 
        , a.PropertyAttributeValue  as Mileage
        , b.PropertyAttributeValue  as Engine
        , c.PropertyAttributeValue  as Manufacturer       
        , d.PropertyAttributeValue  as Seat_Capacity          
from my_table a 
inner join my_table b on a.PropertyAttributeValueID = b.PropertyAttributeValueID 
        and a.PropertyAttributeName='Mileage'
            and b.PropertyAttributeName = 'Engine'
inner join my_table c on a.PropertyAttributeValueID = c.PropertyAttributeValueID 
        and c.PropertyAttributeName='Manufacturer'
inner join my_table d on a.PropertyAttributeValueID = d.PropertyAttributeValueID 
        and d.PropertyAttributeName='Seat_Capacity'

for better performance you could add a composite index on the table

create index  my_idx on my_table ( PropertyAttributeName
                , PropertyAttributeValueID 
               , PropertyAttributeValue);