Cellule à la comparaison de cellules dans Sql Server?

J'ai tbl1 :

  Id | c1 | c2 | c3 | ____|_____|______|_______|_ 1 abc ____|_____|______|_______|_ 2 hjk ____|_____|______|_______|_ 3 tyu ____|_____|______|_______|_ 

J'ai tbl2 :

  Id | c1 | c2 | c3 | ____|_____|______|_______|_ 1 ab D ____|_____|______|_______|_ 2 ccc ____|_____|______|_______|_ 3 klk ____|_____|______|_______|_ 

J'ai besoin de comparer chaque cellule de tbl1 à sa place appropriée dans tbl2 :

la sortie désirée est:

  Id |tbl1 | tbl2 | ____|_____|______| 1 aa ____|_____|______| 1 bb ____|_____|______| 1 cd ____|_____|______| 2 hc ____|_____|______| 2 jc ____|_____|______| 2 kc ____|_____|______| ... ... ... ... 

représentation visuelle :

entrez la description de l'image ici

J'ai essayé beaucoup de requêtes … mais pas réussi …

 select T1.id, T1.tbl1, T2.tbl2 from ( select U.id, U.tbl1, U.col from tbl1 unpivot (tbl1 for col in (c1, c2, c3)) U ) T1 inner join ( select U.id, U.tbl2, U.Col from tbl2 unpivot (tbl2 for col in (c1, c2, c3)) U ) T2 on T1.id = T2.id and T1.col = T2.col order by T1.id 

Faites 3 sous-requêtes atomiques et utilisez ensuite avec UNION ALL pour get le résultat final:

 SELECT tbl1.id, tbl1.c1, tbl2.c1 FROM tbl1 INNER JOIN tbl2 on tbl1.id = tbl2.id UNION ALL SELECT tbl1.id, tbl1.c2, tbl2.c2 FROM tbl1 INNER JOIN tbl2 on tbl1.id = tbl2.id UNION ALL SELECT tbl1.id, tbl1.c3, tbl2.c3 FROM tbl1 INNER JOIN tbl2 on tbl1.id = tbl2.id ORDER BY 1 --sort by column 1 (the IDs) 

Vous devez d'abord dériver datatables:

  select Id, C1, 'C1' as C from tbl1 union all select Id, C2, 'C2' as C from tbl1 union all select Id, C3, 'C2' as C from tbl1 union all 

Ensuite, vous pouvez comparer datatables:

  select coalesce( uTbl1.Id,uTbl2.Id) as Id, uTbl1.C, uTbl2.C from ( select Id, C1 as C, 'C1' as T from tbl1 union all select Id, C2 as C, 'C2' as T from tbl1 union all select Id, C3 as C, 'C3' as T from tbl1 ) uTbl1 full outer join ( select Id, C1 as C, 'C1' as T from tbl2 union all select Id, C2 as C, 'C2' as T from tbl2 union all select Id, C3 as C, 'C3' as T from tbl2 ) uTbl2 on uTbl1.Id = uTbl2.Id and uTbl1.T = uTbl2.T 

Avertissement: – Non testé.

Édité avec CTE:

  ; with uTbl1 as ( select Id, C1 as C, 'C1' as T from tbl1 union all select Id, C2 as C, 'C2' as T from tbl1 union all select Id, C3 as C, 'C3' as T from tbl1 ) ,uTbl2 as ( select Id, C1 as C, 'C1' as T from tbl2 union all select Id, C2 as C, 'C2' as T from tbl2 union all select Id, C3 as C, 'C3' as T from tbl2 ) select coalesce( uTbl1.Id,uTbl2.Id) as Id, uTbl1.C, uTbl2.C from uTbl1 full outer join uTbl2 on uTbl1.Id = uTbl2.Id and uTbl1.T = uTbl2.T