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