CODEWITHSUNDEEP
sql-servert-sql
Scott
Scott

Reputation: 13941

SQL pivot to flatten rows into columns

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.

Upvotes: 5

Views: 5895

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82020

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

Upvotes: 6

Janine
Janine

Reputation: 61

Unfortunately if you need to pivot 3 columns you need to create 3 separate pivots, then merge them in the end:

SELECT  MAX(Year1) as 'Year1', MAX(Make1) as Make1, MAX(Model1) as 'Model1', 
        MAX(Year2) as 'Year2', MAX(Make2) as Make2, MAX(Model2) as 'Model2'

FROM
(
    SELECT  parentid,
            [1] as 'Year1', NULL as Make1, NULL as 'model1', 
            [2] as 'Year2', NULL as Make2, NULL as 'model2'
    FROM (
    SELECT 
        parentid, vehicleindex, model, make , [year]
    FROM #temp
    ) as s
    PIVOT
    (
        max([year])
        FOR vehicleindex IN ([1],[2])
    ) AS yearPvt

UNION

    SELECT  parentid,
        NULL as 'Year1', [1] as Make1, NULL as 'model1', 
        NULL as 'Year2', [2] as Make2, NULL as 'model2'
    FROM
    (
        SELECT 
            parentid, vehicleindex, model, make , [year]
        FROM #temp
        ) as s
       parentid, vehicleindex, model, make , [year]
       FROM #temp
       ) as s
   PIVOT
   (
       MAX([make])
       FOR vehicleindex IN ([1],[2])
    )AS makePvt

UNION

    SELECT  parentid,
        NULL as 'Year1', NULL as Make1, [1]  as 'model1', 
        NULL as 'Year2', NULL  as Make2, [2] as 'model2'
    FROM
    (
      SELECT 
          parentid, vehicleindex, model, make , [year]
      FROM #temp
    ) as s
    PIVOT
    (
       max([model])
       FOR vehicleindex IN ([1],[2])
    ) AS modelPvt

) allPivots
Group BY parentid

Upvotes: 1

SqlZim
SqlZim

Reputation: 38073

Using conditional aggregation:

select 
    parentid
  , year1  = max(case when vehicleindex=1 then [year] end)
  , make1  = max(case when vehicleindex=1 then make end)
  , model1 = max(case when vehicleindex=1 then model end)
  , year2  = max(case when vehicleindex=2 then [year] end)
  , make2  = max(case when vehicleindex=2 then make end)
  , model2 = max(case when vehicleindex=2 then model end)
from t
group by parentid

returns:

+----------+-------+------------+-----------+-------+------------+-----------+
| parentid | year1 |   make1    |  model1   | year2 |   make2    |  model2   |
+----------+-------+------------+-----------+-------+------------+-----------+
|        1 |  2007 | TOYOTA     | SIENNA LE | 2005  | VOLKSWAGEN | JETTA GLS |
|        2 |  2018 | TESLA      | MODEL 3   | NULL  | NULL       | NULL      |
+----------+-------+------------+-----------+-------+------------+-----------+

rextestder demo: http://rextester.com/ZTGXU25389

using this test data:

create table t (
    vehicleindex int
  , parentid int
  , [year] int
  , make varchar(32)
  , model  varchar(32)
);
insert into t values 
  (1,1,2007,'TOYOTA    ','SIENNA LE')
, (2,1,2005,'VOLKSWAGEN','JETTA GLS')
, (1,2,2018,'TESLA','MODEL 3')

Upvotes: 1

Related Questions