split a row into multiple rows in sql server according to a specific column

Question

I have table a having fields id,val and identical table b having fields id,val.

when i am writing an inner join for different data in rows

SELECT * FROM a left join b on a.id = b.id WHERE ( a.id != b.id or a.val != b.val)

i am getting like below

i want to split this table row into two rows. Help me to write a query to split this data and getting an output table like below

Help!

Answer 1

You can generate the extra row(s) by doing an unpivot using a cross apply and the table value constructor.

select c.id,
       c.val
from dbo.a 
  inner join dbo.b
    on a.id = b.id
  cross apply (values(a.id, a.val),
                     (b.id, b.val)) as c(id, val)
where a.val <> b.val;

Answer 2

As far as your query uses a LEFT JOIN b ON a.id = b.id you can change your WHERE clause to WHERE (a.val != b.val) because you wont receive any record where a.id <> b.id

That said, to divide the results you can use a UNION by selecting all records from table a, plus all records from table b that match your WHERE clause.

create table a (id int, val varchar(10));
create table b (id int, val varchar(10));
insert into a values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
insert into b values (1,'ddd'),(5,'bbb'),(3,'xxx'),(7,'ddd');
GO

8 rows affected

SELECT * FROM a left join b on a.id = b.id WHERE ( a.id != b.id or a.val != b.val)
GO

id | val | id | val
-: | :-- | -: | :--
 1 | aaa |  1 | ddd
 3 | ccc |  3 | xxx

SELECT a.* FROM a left join b on a.id = b.id WHERE (a.val <> b.val)
UNION
SELECT b.* FROM a left join b on a.id = b.id WHERE (a.val <> b.val)
GO

id | val
-: | :--
 1 | aaa
 1 | ddd
 3 | ccc
 3 | xxx

dbfiddle here