creating a sequence of number starting or each group of ID

Question

I have 3 source table and have to union them all

Source 1:

Id   name  car   
-------------------
1    aaa    BMW   
1    aaa    Porche     
1    aaa    Nissan
2    bbb    Nissan
2    bbb    Honda

Source 2:

Id   name  car   
-------------------
1    aaa    Camry   

Source 3:

Id   name  car   
---------------------
1    aaa    Honda   
1    aaa    Ford     
2    bbb    Buick

Now I want to combine them all and insert into a table with a sequence. this sequence will have incremental value repeating for each ID.

Target table:

Id   name    car   sequence
--------------------------------
1    aaa    BMW        1
1    aaa    Porche     2 
1    aaa    Nissan     3
1    aaa    Camry      4
1    aaa    Honda      5
1    aaa    Ford       6
2    bbb    Nissan     1
2    bbb    Honda      2
2    bbb    Buick      3

I know I have to use row_number() and partition by but unable to capture the sequence with Union with this function. However, I can insert them in temp table and create another temp table with row_number, it works fine.

I unable to update the column with row_number() as well. I wanted to know if there is direct approach with union or if I can update the target table?

Answer 1

Not sure I entirely understood your problem, but this would select the sequence IDs using ROW_NUMBER for the union of three tables:

;WITH ct AS (
    SELECT *
    FROM (VALUES(1,'aaa','BMW'),(1,'aaa','Porsche'),(2,'bbb','Nissan'),(2,'bbb','Honda')) AS T(id,name,car)
    UNION ALL
    SELECT *
    FROM (VALUES(1,'aaa','Camry')) AS T(id,name,car)
    UNION ALL
    SELECT *
    FROM (VALUES(1,'aaa','Honda'),(2,'bbb','Buick')) AS T(id,name,car)
)
SELECT
    *,
    rn=ROW_NUMBER() OVER (PARTITION BY name ORDER BY car)
FROM
    ct;

Which results in:

╔════╦══════╦═════════╦════╗
║ id ║ name ║   car   ║ rn ║
╠════╬══════╬═════════╬════╣
║  1 ║ aaa  ║ BMW     ║  1 ║
║  1 ║ aaa  ║ Camry   ║  2 ║
║  1 ║ aaa  ║ Honda   ║  3 ║
║  1 ║ aaa  ║ Porsche ║  4 ║
║  2 ║ bbb  ║ Buick   ║  1 ║
║  2 ║ bbb  ║ Honda   ║  2 ║
║  2 ║ bbb  ║ Nissan  ║  3 ║
╚════╩══════╩═════════╩════╝