UNPIVOT 如何为每个 ID 添加增量行号 [英] UNPIVOT how to add incremental row numbers per ID

问题描述

我有以下代码，我对结果很满意

I have the following code which I am already happy with the results

SELECT AirlineStatementID, SegmentFrom, SegmentTo, Class, TravelDate, AirlineCode
FROM 
   (SELECT AirlineStatementID
      ,[SegmentFrom1]
      ,[SegmentFrom2]
      ,[SegmentFrom3]
      ,[SegmentFrom4]
      ,[SegmentTo1]
      ,[SegmentTo2]
      ,[SegmentTo3]
      ,[SegmentTo4]
      ,[Class1]
      ,[Class2]
      ,[Class3]
      ,[Class4]
      ,[TravelDate1] 
      ,[TravelDate2]
      ,[TravelDate3]
      ,[TravelDate4]
      ,[AirlineCode1]
      ,[AirlineCode2]
      ,[AirlineCode3]
      ,[AirlineCode4]
   FROM AirlineStatements) p
UNPIVOT
   (SegmentFrom FOR V IN (SegmentFrom1, SegmentFrom2, SegmentFrom3, SegmentFrom4)) as A
UNPIVOT   
   (SegmentTo FOR W IN (SegmentTo1, SegmentTo2, SegmentTo3, SegmentTo4)) as B
UNPIVOT   
   (Class FOR X IN (Class1, Class2, Class3, Class4)) as C
UNPIVOT   
   (TravelDate FOR Y IN (TravelDate1, TravelDate2, TravelDate3, TravelDate4)) as D
UNPIVOT   
   (AirlineCode FOR Z IN (AirlineCode1, AirlineCode2, AirlineCode3, AirlineCode4)) as E 

数据库中的结果看起来像这样

The reults in the database would look like such

AirlineStatementID | SegmentFrom | SegmentTo | Class | TravelDate | AirlineCode
1063 | WLG | CHC | B | 2010-09-01 00:00:00.000 | NZ
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ
1064 | WLG | CHC | M | 2010-09-15 00:00:00.000 | NZ
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ
1065 | WLG | CHC | Y | 2010-08-25 00:00:00.000 | NZ
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ

我想要的是列上的行号，所以它反映如下

What I want is a row number on column so it reflects like this

AirlineStatementID | SegmentFrom | SegmentTo | Class | TravelDate | AirlineCode | Sequence
1063 | WLG | CHC | B | 2010-09-01 00:00:00.000 | NZ | 1
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ | 2
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ | 3
1063 | WLG | CHC | B | 1800-01-01 00:00:00.000 | NZ | 4
1064 | WLG | CHC | M | 2010-09-15 00:00:00.000 | NZ | 1
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ | 2
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ | 3
1064 | WLG | CHC | M | 1800-01-01 00:00:00.000 | NZ | 4
1065 | WLG | CHC | Y | 2010-08-25 00:00:00.000 | NZ | 1
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ | 2
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ | 3
1065 | WLG | CHC | Y | 1800-01-01 00:00:00.000 | NZ | 4

谁能帮帮我

推荐答案

在 SELECT

ROW_NUMBER() OVER (PARTITION BY [AirlineStatementID] ORDER BY [TravelDate] DESC) AS [Sequence]

您可以在 ORDER BY 中添加其他列以支持您需要的任何特定排序

You can add additional columns in the ORDER BY to support any specific sorting you need

这篇关于UNPIVOT 如何为每个 ID 添加增量行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！