SQL pivot to flatten rows into columns

Question

I have a source table that looks like this:

+--------------+----------+------+------------+-----------+
| vehicleindex | parentid | year |    make    |   model   |
+--------------+----------+------+------------+-----------+
|            1 |        1 | 2007 | TOYOTA     | SIENNA LE |
|            2 |        1 | 2005 | VOLKSWAGEN | JETTA GLS |
+--------------+----------+------+------------+-----------+

I'd like to select from this table such that the output looks like this:

+-------+--------+-----------+-------+------------+-----------+
| year1 | make1  |  model1   | year2 |   make2    |  model2   |
+-------+--------+-----------+-------+------------+-----------+
|  2007 | TOYOTA | SIELLA LE |  2005 | VOLKSWAGEN | JETTA GLS |
+-------+--------+-----------+-------+------------+-----------+

How can I accomplish this on a SQL Server database with a pivot? There will always be either 1 or 2 vehicles in the source table. In the case where there's 1 vehicle, I would expect Year2, Make2 and Model2 to be NULL.

Answer 1

Similar to SQLZim's answer. Only difference is that the Window function Row_Number() is used just in case vehicleindex is not a consistent 1 and 2.

Select year1  = max(case when RN=1 then [year] end)
      ,make1  = max(case when RN=1 then make end)
      ,model1 = max(case when RN=1 then model end)
      ,year2  = max(case when RN=2 then [year] end)
      ,make2  = max(case when RN=2 then make end)
      ,model2 = max(case when RN=2 then model end)
 From (
        Select *
              ,RN = Row_Number() over (Partition By parentid Order By vehicleindex)
         From  YourTable
      ) A
 Group By parentid 

EDIT: Option 2 - Use PIVOT

Select *
From (
        Select parentid
              ,item     = concat(B.item,Dense_Rank() over (Partition By parentid Order By vehicleindex))
              ,value
         From  YourTable
         Cross Apply ( values ('year' ,cast(Year as varchar(100)))
                             ,('make' ,make)
                             ,('model',model)
                      ) B (item,value)
     ) A
 Pivot (max(value) For [item] in ([year1],[make1],[model1],[year2],[make2],[model2]) ) p