SQL: Select another column from a column that has the same value-CodePudding

I would like to select ORDER_NUMBER from that same group of ID. Which the pairing conditions are as follows:

A pairs with B (A is the main tag order. B is the minor tag order) and B pairs with C

X pairs with Y (X is the main tag order. Y is the minor tag order) and Y pairs with Z

  ID   ORDER    ORDER_NUMBER        Tag
  --- -------- ----------------- ---------
  47   48      200000002814           A
  47   49      30000000266600001      B
  47   49      30000000266600000      C
  50   51      200000001999           A
  50   52      30000000266600002      B
  50   52      30000000266605763      C
  ..   ...     ...                    ..
  ..   ...     ...                    ..
  250  251     200000001932           X
  250  252     30000000266600121      Y
  250  252     30000000266605452      Z

Expected result:

   Tag_main| Tag_minor| ORDER_NUMBER_main  | ORDER_NUMBER_minor
------------- -------------- --------------
    A      |    B     | 200000002814       |  30000000266600001
    B      |    C     | 30000000266600001  |  30000000266600000      
    A      |    B     | 200000001999       |  30000000266600002
    B      |    C     | 30000000266600002  |  30000000266605763  
    X      |    Y     | 200000001932       |  30000000266600121
    Y      |    Z     | 30000000266600121  |  30000000266605452

How can I write the code?

Thank you for your help

CodePudding user response：

Using ROW_NUMBER along with a self join we can try:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Tag) rn
    FROM yourTable t
)

SELECT
    t1.Tag AS Tag_main,
    t2.Tag AS Tag_minor,
    t1.ORDER_NUMBER AS ORDER_NUMBER_main,
    t2.ORDER_NUMBER AS ORDER_NUMBER_minor
FROM cte t1
INNER JOIN cte t2
    ON t2.ID = t1.ID AND t2.rn = t1.rn   1
ORDER BY
    t1.ID,
    t1.Tag;