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 enter image description here

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 enter image description here

Help!

• McNets: left join on a.id = b.id wont return any (a.id <> b.id)

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;

• Paul White: Comments moved to chat as requested.

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

• RAMEEHA: Perfect!!!!!!!!!!!!!!!!!!!!! it Works for me
• Pardeep Sharma: If it works pls accept this as an Ans