問題描述

我有一個看起來像這樣的源表:

+--------------+---------+------+-----------+-----------+|車輛索引 |父母 |年 |制作|模型 |+--------------+----------+------+------------+-----------+|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 數據透視表將行展平為列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！