CODEWITHSUNDEEP
sql
sindhu Y
sindhu Y

Reputation: 15

How to calculate the number of columns got changed by comparing two tables with column names in sql

How to calculate the number of columns got changed by comparing two tables with same column names based on joining key 'id' in sql.

Table 1
id val1  val2  val3  val4
1   a    b    c    d
2   d    f    k    e
4   r    t    y    u

Table 2
id val1 val2 val3 val4
1   a    h    c    l
2   d    f    k    e
4   g    a    w    u

count:
id  count
1    2
2    0
4    3

I want this count variable to be added to the second table at last and give the count of how many values got changed from previous table to this.

Upvotes: 1

Views: 70

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

DECLARE @Table1 TABLE (id INT, val1 VARCHAR(5),  val2 VARCHAR(5), val3 VARCHAR(5), val4 VARCHAR(5))
INSERT INTO @Table1 VALUES
(1,'a', 'b','c','d'),
(2,'d', 'f','k','e'),
(4,'r', 't','y','u')

DECLARE @Table2 TABLE (id INT, val1 VARCHAR(5),  val2 VARCHAR(5), val3 VARCHAR(5), val4 VARCHAR(5))
INSERT INTO @Table2 VALUES
(1, 'a','h','c','l'),
(2, 'd','f','k','e'),
(4, 'g','a','w','u')

SELECT T1.id,  
 (CASE WHEN T1.val1 <> T2.val1 THEN 1 ELSE 0 END)
 + (CASE WHEN T1.val2 <> T2.val2 THEN 1 ELSE 0  END)
 + (CASE WHEN T1.val3 <> T2.val3 THEN 1 ELSE 0 END)
 + (CASE WHEN T1.val4 <> T2.val4 THEN 1 ELSE 0 END) AS [count]
FROM @Table1 T1 INNER JOIN @Table2 T2
ON T1.id = T2.id

or you can use UNPIVOT

SELECT T1.id, SUM (CASE WHEN T1.Val = T2.Val THEN 0 ELSE 1 END) [count] FROM
(SELECT * FROM @Table1 UNPIVOT ( Val FOR Col IN ([val1],[val2],[val3],[val4]) ) UNPVT) T1
    INNER JOIN
(SELECT * FROM @Table2 UNPIVOT ( Val FOR Col IN ([val1],[val2],[val3],[val4]) ) UNPVT) T2
ON T1.id = T2.id AND T1.Col = T2.Col
GROUP BY T1.id

Result:

id  count
1    2
2    0
4    3

Upvotes: 3

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this

SELECT t1.ID,SUM(CASE WHEN t1.VAL1 <> t2.Val1 THEN 1 ELSE 0 END + 
                 CASE WHEN t1.VAL2 <> t2.Val2 THEN 1 ELSE 0 END + 
                 CASE WHEN t1.VAL3 <> t2.Val3 THEN 1 ELSE 0 END +
                 CASE WHEN t1.VAL4 <> t2.Val4 THEN 1 ELSE 0 END )
FROM Table1 t1
INNER JOIN table2 t2 ON t1.ID=t2.ID
GROUP BY t1.ID

Upvotes: 2

Related Questions