I would like to know if two tables 'table1' and 'table2' are identical. I know I could compare every column of both tables in the 'where'-clause So this would basically show me every Row that is identical.

But what I want to know is which columns are identical. So I think it would be easy to just transpone the tables and compare the results as mentioned before. Column names and order in the tables are both identical as already given. I made also an example Input and Output scenario:

-------Input--------------- table1

table2

----------Output----------------

result

OR result

OR result

OR result

OR similar..... The values of the non identical columns dont matter I just need the column name, but I wouldnt care if values would come along with it. I hope its not too diffcult.

CodePudding user response：

Consifering you really want to receive only the identical columns, you might want to try an approach using unpivot. Following an example:

DECLARE @t1 TABLE(
   id int
  ,col1 int
  ,col2 int
  ,col3 int
);

INSERT INTO @t1 VALUES
 (1,14,23,45)
,(2,12,21,43)
,(3,12,22,43)
,(4,10,12,41)
,(5,11,23,44)
,(6,13,25,43);

DECLARE @t2 TABLE(
   id int
  ,col1 int
  ,col2 int
  ,col3 int
);

INSERT INTO @t2 VALUES
 (1,14,20,45)
,(2,12,0,43)
,(3,12,22,43)
,(4,10,30,41)
,(5,11,23,44)
,(6,13,NULL,43);

WITH cte1 AS(
  SELECT id, col, val
    FROM (SELECT id, col1, col2, col3 FROM @t1) p
  UNPIVOT
    (val FOR col IN (col1, col2, col3)) as unpvt
),
cte2 AS(
  SELECT id, col, val
    FROM (SELECT id, col1, col2, col3 FROM @t2) p
  UNPIVOT
    (val FOR col IN (col1, col2, col3)) as unpvt
)
SELECT DISTINCT c1.id, c1.col, c1.val
  FROM cte1 c1
  INNER JOIN cte2 c2 ON c2.id = c1.id AND c2.col = c1.col AND c2.val = c1.val
  ORDER BY 1, 2