获取两个行的值不同的列名

I have a table with lots of columns. Sometimes I need to find differences between two rows. I can do it just by scrolling through screen but it is dull. I'm looking for a query that will do this for me, something like

我有一张有很多列的桌子。有时我需要找出两行之间的差异。我只需要在屏幕上滚动就可以了，但它很无聊。我在寻找一个查询，它可以为我做这些，比如

SELECT columns_for_id_1 != columns_for_id_2
FROM  xyz
WHERE id in (1,2)

Table:

表:

id col1 col2 col3 col4
1  qqq  www  eee  rrr
2  qqq  www  XXX  rrr

Result:

结果:

"Different columns: id, col3"

Is there a simple way to do this?

有简单的方法吗?

UPDATE

更新

Another example as wanted: What I have (table has more than 50 column, not only 7):

另一个想要的例子:我有(表有50多栏，不只是7):

Id| Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
==============================================
1 | aaa  | bbb  | ccc  | ddd  | eee  | fff  |
----------------------------------------------
2 | aaa  | XXX  | ccc  | YYY  | eee  | fff  |

Query:

查询:

SELECT *
FROM table
WHERE Id = 1 OR Id = 2 
AND "columns value differs"

Query result: "Id, Col2, Col4"

查询结果:“Id, Col2, Col4”

OR something like: Id|Col2 |Col4 | =============== 1 |bbb |ddd | --------------- 2 |XXX |YYY |

或者类似的:Id | Col2 | Col4 | = = = = = = = = = = = = = = = 1 | bbb ddd | | - - - - - - - - - - - - - - - - - - 2 | XXX | YYY |

Right now I have to scroll through more than 50 columns to see if rows are the same, it's not efficient and prone to mistakes. I don't want any long query like SELECT (COMPARE Id1.Col1 with Id2.Col1 if different then print "Col1 differs", COMPARE Id1.Col2 with Id2.Col2...) because I will compare the rows myself faster ;)

现在，我必须滚动浏览超过50列，看看行是否相同，这不是有效的，而且容易出错。我不需要像SELECT(比较Id1)这样的长查询。Col1 Id2下。Col1如果不同则打印“Col1不同”，比较Id1。Col2和Id2.Col2…)因为我自己比较行的速度更快;

3 个解决方案

#1

Something like this:

是这样的:

SELECT col, MIN(VAL) AS val1, MAX(val) AS val2
FROM (
SELECT id, val, col
FROM (
   SELECT id,  [col1], [col2], [col3], [col4]
   FROM mytable
   WHERE id IN (1,2)) AS src
UNPIVOT (
   val FOR col IN ([col1], [col2], [col3], [col4])) AS unpvt ) AS t
GROUP BY col 
HAVING MIN(val) <> MAX(val)

Output:

输出:

col  val1 val2
================
col3 eee  XXX

#2

Try this simple query, may be help you

尝试这个简单的查询，可能会对您有所帮助

    SELECT (CASE WHEN a.col1 <> b.col1 THEN 'Different Col1' 
                 WHEN a.col2 <> b.col2 THEN 'Different Col2' 
                 ...
                 ELSE 'No Different' END) --You can add only required columns here
    FROM xyz AS a 
    INNER JOIN xyz AS b ON b.id = 1 --First Record
    WHERE a.id = 2 --Second record to compare

#3

If you are on SQL Server 2012 then you can also use LEAD/LAG windowed funuction to do this. MSDN Reference is here - https://msdn.microsoft.com/en-us/library/hh213125.aspx

如果您使用的是SQL Server 2012，那么您也可以使用LEAD/LAG窗口函数来实现这一点。MSDN引用在这里——https://msdn.microsoft.com/en-us/library/hh213125.aspx

select 
id, 
col1,
col2,
col3,
col4,
stuff(diff_cols,len(diff_cols-1),1,'') diff_cols 
from 
(
SELECT 
id, 
col1,
col2,
col3,
col4,
concat
(
'Different columns:',
CASE 
WHEN LEAD(id, 1,0) OVER (ORDER BY id) <> id THEN 'id,'
WHEN LEAD(col1, 1,0) OVER (ORDER BY id) <> col1 THEN 'col1,'
WHEN LEAD(col2, 1,0) OVER (ORDER BY id) <> col2 THEN 'col2,'
WHEN LEAD(col3, 1,0) OVER (ORDER BY id) <> col3 THEN 'col3,'
WHEN LEAD(col4, 1,0) OVER (ORDER BY id) <> col4 THEN 'col4,'
) diff_cols
FROM xyz
) tmp

#1