2

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!

  • left join on a.id = b.id wont return any (a.id <> b.id) – McNets Apr 13 '17 at 7:38
4

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;
0

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

  • Perfect!!!!!!!!!!!!!!!!!!!!! it Works for me – RAMEEHA Apr 13 '17 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.