SQL 横向旋转以将行转换为列

Question

我有一个源表格，长这样：

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

我想从这个表中选择，使输出看起来像这样:

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

如何使用SQL Server数据库与枢轴表完成此操作？源表中始终会有1或2辆车。在只有1辆车的情况下，我希望Year2、Make2和Model2为NULL。

- Scott

---

这两行之间有什么共同点？如果有多辆车，你会如何处理？ - Utsav

更新了源表。 - Scott

Answer 1

与SQLZim的回答类似。唯一的区别在于，Window函数Row_Number()用于情况vehicleindex不一致的情况下。

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 

编辑：选项2 - 使用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

- John Cappelletti

Answer 2

使用条件聚合：

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

返回：

+----------+-------+------------+-----------+-------+------------+-----------+
| 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演示：http://rextester.com/ZTGXU25389

使用此测试数据：

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')

- SqlZim

Answer 3

不幸的是，如果您需要旋转三列，则需要创建三个单独的旋转表，最后再合并它们：

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

- Janine