CODEWITHSUNDEEP
sql-servert-sql
user7083213
user7083213

Reputation: 23

Same ID in multiple rows using SQL Server

I am working on two tables with column headers ID_1, X1, Value as below

Table 1: 

ID_1| X1  | Value
A01 | A   | 10 
A01 | B   | 5
A02 | B   | 3
A03 | A   | 4
A02 | A   | 8

Table 2:

ID_1| X1  | Value
Z01 | A   | 7 
Z01 | B   | 2 
Z01 | C   | 9 
Z03 | A   | 1 
Z02 | B   | 3 
Z03 | B   | 6

I want to combine these two tables with and additional column with header N at the beginning as below (Expected Output):

N  |ID_1| X1  | Value
1  |A01 | A   | 10
1  |A01 | B   | 5
2  |A02 | B   | 3
2  |A02 | A   | 8
3  |A03 | A   | 4
4  |Z01 | A   | 7
4  |Z01 | B   | 2
4  |Z01 | C   | 9
5  |Z02 | B   | 3
6  |Z03 | A   | 1
6  |Z03 | B   | 6

Upvotes: 1

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I think you want union all and dense_rank():

select dense_rank() over (order by id_1) as n,
       id_1, x, value
from ((select id_1, x, value from t1
      ) union all
      (select id_1, x, value from t2
      )
     ) t;

Upvotes: 3

Related Questions