I have a table with two columns Item, Qty and another table with Product, Quantity.
Table A
| Item | Qty |
|---|---|
| a | 10 |
| a | 15 |
| a | 5 |
| b | 10 |
Table b
| Product | Quantity |
|---|---|
| a | 10 |
| a | 20 |
| b | 5 |
| b | 5 |
The output that I'm looking for is this:
| item | Qty | Product | Quantity |
|---|---|---|---|
| a | 10 | a | 10 |
| a | 15 | a | 20 |
| a | 5 | NULL | NULL |
| b | 10 | b | 5 |
| NULL | NULL | b | 5 |
CodePudding user response：
You will need to have some sort of order to guarantee consistent results. To simulate that, I added IDENTITY columns
Match Product to Item in Order Based on ROW_NUMBER()
DROP TABLE IF EXISTS #Table1
DROP TABLE IF EXISTS #Table2
CREATE TABLE #Table1 (ID INT IDENTITY(1,1),Item CHAR(1),Qty INT)
CREATE TABLE #Table2 (ID INT IDENTITY(1,1),Product CHAR(1),Qty INT)
INSERT INTO #Table1
VALUES ('a',10)
,('a',15)
,('a',5)
,('b',10)
INSERT INTO #Table2
VALUES ('a',10)
,('a',20)
,('b',5)
,('b',5)
;WITH cte_Table1 AS (
SELECT *,RankNum = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ID)
FROM #Table1
),
cte_Table2 AS (
SELECT *,RankNum = ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ID)
FROM #Table2
)
SELECT *
FROM cte_Table1 AS A
FULL JOIN cte_Table2 AS B
ON A.Item = B.Product
AND A.RankNum = B.RankNum