SQL 数据透视表将行展平为列 [英] SQL pivot to flatten rows into columns

问题描述

我有一个看起来像这样的源表:

+--------------+---------+------+-----------+-----------+|车辆索引 |父母 |年 |制作|模型 |+--------------+----------+------+------------+-----------+|1 |1 |2007 |丰田 |SIENNA LE ||2 |1 |2005 |大众 |捷达GLS |+--------------+----------+------+------------+-----------+

我想从该表中进行选择，以便输出如下所示:

+-------+--------+---------+-------+------------+--------------+|第一年 |制作1 |型号1 |年2 |制作2 |模型2 |+-------+--------+-----------+-------+------------+-----------+|2007 |丰田 |塞拉·勒 |2005 |大众 |捷达GLS |+-------+--------+-----------+-------+------------+-----------+

如何在带有数据透视表的 SQL Server 数据库上完成此操作?源表中总是有 1 或 2 辆车.如果有 1 辆车，我希望 Year2、Make2 和 Model2 为 NULL.>

解决方案

类似于 SQLZim 的回答.唯一的区别是使用 Window 函数 Row_Number() 以防 vehicleindex 不是一致的 1 和 2.

Select year1 = max(case when RN=1 then [year] end),make1 = max(当RN=1时make结束),model1 = max(当RN=1时模​​型结束),year2 = max(当 RN=2 然后 [year] 结束的情况),make2 = max(当RN=2时make结束),model2 = max(当RN=2时模型结束)从 (选择 *,RN = Row_Number() over (Partition By parentid Order By Vehicleindex)从你的表) 一种按父ID分组

<块引用>

选项 2 - 使用 PIVOT

选择*从 (选择 parentid,item = concat(B.item,Dense_Rank() over (Partition By parentid Order By Vehicleindex))，价值从你的表交叉应用 ( values ('year' ,cast(Year as varchar(100))),('制作',制作),('模型',模型)) B(项目，价值)) 一种Pivot (max(value) For [item] in ([year1],[make1],[model1],[year2],[make2],[model2]) ) p

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.

解决方案

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

这篇关于SQL 数据透视表将行展平为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！