Combine two tables - sql server

Question

I'm trying to combine 2 tables in SQL Server

Table 1: SO

ItemCode     | SONumber| SODate     | SOQTY
-------------------------------------------
TBJ182-01-02 | 0005251 | 29/01/2014 | 5
TBJ184-01-02 | 0005251 | 29/01/2014 | 2
TBJ182-01-02 | 0005554 | 15/02/2014 | 4
TBJ185-01-02 | 0005554 | 15/02/2014 | 5

Table 2: PO

ItemCode     | PONumber| PODate     | POQTY
--------------------------------------------
TBJ182-01-02 | 0009105 | 11/02/2014 | 8
TBJ184-01-02 | 0009208 | 14/02/2014 | 5
TBJ189-01-02 | 0009208 | 14/02/2014 | 5

Result table:

ItemCode     | SONumber| SODate    | SOQTY | PONmber |PODate      | POQTY
-------------------------------------------------------------------------
TBJ182-01-02 | 0005251 | 29/01/2014| 5     |         |            |
TBJ184-01-02 | 0005251 | 29/01/2014| 2     |         |            |
TBJ182-01-02 | 0005554 | 15/02/2014| 4     |         |            |
TBJ185-01-02 | 0005554 | 15/02/2014| 5     |         |            |
TBJ182-01-02 |         |           |       | 0009105 | 11/02/2014 | 8
TBJ184-01-02 |         |           |       | 0009208 | 14/02/2014 | 5
TBJ189-01-02 |         |           |       | 0009208 | 14/02/2014 | 5

Could you help?

Are you looking to combine the tables permanently? or only want to output it via a select statement? — Duniyadnd, Apr 30 '14 at 21:37

score 2 · Accepted Answer · answered Apr 30 '14 at 21:38

2

You can do this most easily with a full outer join and a little trick:

select coalesce(SO.ItemCode, PO.ItemCode) as ItemCode,
       SO.SONumber, SO.SODate, SO.SOQTY,
       PO.PONmber, PO.PODate, PO.POQTY
from SO full outer join
     PO
     on 1 = 0;

answered Apr 30 '14 at 21:38

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon. It works as expected. I may have small amendment to tables layout in my original question. I'll ask if needed. – kufer May 01 '14 at 13:06

score 0 · Answer 2 · answered May 01 '14 at 07:01

Nothing more i have modified removed outer and ISNULL in place of coalesce

DECLARE @Tbl TABLE (


     ITEMCode VARCHAR(100),
        SONUMBER VARCHAR(100), 
        SoQTY INT
    )

    INSERT INTO @Tbl VALUES ('TBJ182-01-02','0005251',1)
    INSERT INTO @Tbl VALUES ('TBJ184-01-02', '0005251', 2)
    INSERT INTO @Tbl VALUES ('TBJ182-01-02', '0005554',4)
    INSERT INTO @Tbl VALUES ('TBJ182-01-02', '0005554',6)

    DECLARE @Tbl1 TABLE (
        ITEMCode VARCHAR(100),
        PONUMBER VARCHAR(100), 
        POQTY INT
    )

    INSERT INTO @Tbl1 VALUES ('TBJ182-01-02','0005251',1)
    INSERT INTO @Tbl1 VALUES ('TBJ184-01-02', '0005251', 2)
    INSERT INTO @Tbl1 VALUES ('TBJ182-01-02', '0005554',4)
    INSERT INTO @Tbl1 VALUES ('TBJ182-01-02', '0005554',6)
    select ISNULL(t.ITEMCode,tt.ITEMCode),t.SONUMBER,t.SoQTY,tt.PONUMBER,tt.POQTY from @Tbl t
    FULL JOIN @Tbl1 tt
    ON 1 = 0

Combine two tables - sql server

2 Answers2