I want to merge 2 tables into 1 table
Data inserted into blank rows of the remaining table
Table inclusion criteria are based on group columns
Hope you can help me. I have attached an illustration to make it easier for you to understand what I mean.
Sample image attached
Table1:
| ColGroup | Col1 | Col2 | Col3 |
|---|---|---|---|
| A | A11 | A12 | A13 |
| A | A21 | A22 | A23 |
| A | A31 | A32 | A33 |
| C | C11 | C12 | C13 |
| C | C21 | C22 | C23 |
Table2
| ColGroup | Col4 | Col5 | Col6 | col7 |
|---|---|---|---|---|
| A | A111 | A121 | A131 | A141 |
| A | A211 | A221 | A231 | A241 |
| B | B311 | B321 | B331 | NULL |
| C | C111 | C121 | C131 | C141 |
| C | C211 | C221 | C231 | NULL |
| C | C311 | C321 | C331 | C441 |
I have prepared sample data so you can support me faster
Table1 :
SELECT 'A' AS ColGroup, 'A11' AS col1, 'A12' AS col2, 'A13' AS col3
UNION ALL
SELECT 'A', 'A21', 'A22', 'A23'
UNION ALL
SELECT 'A', 'A31', 'A32', 'A33'
UNION ALL
SELECT 'C', 'C11', 'C12', 'C13'
UNION ALL
SELECT 'C', 'C21', 'C22', 'C23'
Table2 :
SELECT 'A' AS ColGroup, 'A111' AS Col4, 'A121' AS Col5, 'A131' AS Col6, 'A141' AS Col7
UNION ALL
SELECT 'A', 'A211', 'A221', 'A231', 'A241'
UNION ALL
SELECT 'B', 'B311', 'B321', 'B331', 'NULL'
UNION ALL
SELECT 'C', 'C111', 'C121', 'C131', 'C141'
UNION ALL
SELECT 'C', 'C211', 'C221', 'C231', 'NULL'
UNION ALL
SELECT 'C', 'C311', 'C321', 'C331', 'C441'`
I want the results table
| ColGroup | Col1 | -Col2 | Col3 | Col4 | Col5 | Col6 | col7 |
|---|---|---|---|---|---|---|---|
| A | A11 | A12 | A13 | A111 | A121 | A131 | A141 |
| A | A21 | A22 | A23 | A211 | A221 | A231 | A241 |
| A | A31 | A32 | A33 | NULL | NULL | NULL | NULL |
| B | NULL | NULL | NULL | B311 | B321 | B331 | NULL |
| C | C11 | C12 | C13 | C111 | C121 | C131 | C141 |
| C | C21 | C22 | C23 | C211 | C221 | C231 | NULL |
| C | NULL | NULL | NULL | C311 | C321 | -C331 | C441 |