Need help with T sql query

Question

I have two tables. The data in those are something like below:

Table 1:

Col1      Col2
----------------
A          A1
A          A2
A          A3
B          B1
B          B2
B          B3

Table 2:

Col1        Col2
------------------
A            A1
A            A4
A            A5
B            B1
B            B4
B            B5

I need to bring out the difference in data in Col2 of two tables based on the values in Col1. The output should look something like below:

Output:

Col    MismatchValuesInTable1     Mismatchvaluesintable2
---------------------------------------------------------
A               A2                       A4
                A3                       A5
B               B2                       B4
                B3                       B5

Please help me with a query to achieve the above.

Answer 1

This will produce the output table you have. Not sure if the position matters in your problem? If so, this matches based on the fact that the second value for A in table T1 doesn't match the second value for A in table T2.

create table T1
(col1 varchar(10),
col2 varchar(10))

create table T2
(col1 varchar(10),
col2 varchar(10))


insert into T1
select 'A','A1'
union all
select 'A','A2'
union all
select 'A','A3'
union all
select 'B','B1'
union all
select 'B','B2'
union all
select 'B','B3'

insert into T2
select 'A','A1'
union all
select 'A','A4'
union all
select 'A','A5'
union all
select 'B','B1'
union all
select 'B','B4'
union all
select 'B','B5'

select T1.col1, T1.col2, T2.col2
from (select row_number() over(partition by Col1 order by Col1, Col2) as row_number, 
* from T1) as T1
inner join 
(select row_number() over(partition by Col1 order by Col1, Col2) as row_number, 
* from B) as T2
on T1.col1 = T2.col1
and T1.row_number = T2.row_number
where T1.col2 <> T2.col2

Answer 2

select isnull(t1.Col1,t2.Col2) as Col,
  t1.Col2 as MismatchValuesInTable1, 
  t2.Col2 as MismatchValuesInTable2

from t1
FULL JOIN t2 on (t1.Col1=T2.Col1) and (t1.Col2=t2.Col2)
where t1.Col2 is null or t2.Col2 is null
order by Col

SQLFiddle demo