Solved

Get differences

Posted on 2013-10-23
5
268 Views
Last Modified: 2013-10-24
Hi,

Select Col1,Col2,Col3,
From T1
Except

Select Col1,Col2,Col3,
From T2

I have like above query but with more than 3 columns and I got some rows , How can I get a specific column's name that has a difference value?.
0
Comment
Question by:SanPrg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39596166
If I have interpreted your question properly, you want to not only identify the rows that are different, but also which columns of those rows are the cause of the difference.

I'm not personally familiar with any prior solutions to this, but I think the following may give you some ideas. However you will note that that this approach requires running the except query twice, then generating a row_number() on each, then transposing both from row orientation to column orientation - and in the transposition everything gets converted to nvarchar(max).

So: in a nutshell, don't expect this to be quick.

Perhaps others will know of a better approach, but here goes:
**MS SQL Server 2012 Schema Setup**:

    CREATE TABLE Table1
    	([ID] int, [Col1] varchar(50), [Col2] varchar(30), [Col3] varchar(100), [Col4] varchar(60))
    ;
    	
    INSERT INTO Table1
    	([ID], [Col1], [Col2], [Col3], [Col4])
    VALUES
    	(444111, 'Flintstone', 'Fred', '1 Stone St', 'Bedrock')
    ;
    
    CREATE TABLE Table2
    	([ID] int, [Col1] varchar(50), [Col2] varchar(30), [Col3] varchar(100), [Col4] varchar(60))
    ;
    	
    INSERT INTO Table2
    	([ID], [Col1], [Col2], [Col3], [Col4])
    VALUES
    	(555111, 'Flintstone', 'Fred', '5 Pebble St', 'Bedroc')

**Query 1**:

    ;WITH
     cte1 AS (
              SELECT
                    rn
                  , ca1.col_name
                  , ca1.col_value
              FROM (
                    SELECT
                          row_number() over (ORDER BY Col1,Col2,Col3,Col4) AS rn
                        , Col1,Col2,Col3,Col4
                    FROM (
                          SELECT Col1,Col2,Col3,Col4
                          FROM Table1                 --<< nb table order is important
                          EXCEPT
    
                          SELECT Col1,Col2,Col3,Col4
                          FROM Table2                 --<< nb table order is important
                         ) AS x
                   ) AS y
              CROSS APPLY (
                           VALUES
                                  ('Col1',convert(nvarchar(MAX),col1))
                                 ,('Col2',convert(nvarchar(MAX),col2))
                                 ,('Col3',convert(nvarchar(MAX),col3))
                                 ,('Col4',convert(nvarchar(MAX),col4))
                          ) AS ca1 (col_name, col_value)
            )
    ,cte2 AS (
              SELECT
                    rn
                  , ca1.col_name
                  , ca1.col_value
              FROM (
                    SELECT
                          row_number() over (ORDER BY Col1,Col2,Col3,Col4) AS rn
                        , Col1,Col2,Col3,Col4
                    FROM (
                          SELECT Col1,Col2,Col3,Col4
                          FROM Table2                 --<< nb table order is important
                          EXCEPT
    
                          SELECT Col1,Col2,Col3,Col4
                          FROM Table1                 --<< nb table order is important
                         ) AS x
                   ) AS y
              CROSS APPLY (
                           VALUES
                                  ('Col1',convert(nvarchar(MAX),col1))
                                 ,('Col2',convert(nvarchar(MAX),col2))
                                 ,('Col3',convert(nvarchar(MAX),col3))
                                 ,('Col4',convert(nvarchar(MAX),col4))
                          ) AS ca1 (col_name, col_value)
            )
    SELECT
          cte1.rn
        , cte1.col_name  as "Column Name"
        , cte1.col_value as "Table1 Value"
        , cte2.col_value as "Table2 Value"
    FROM cte1
    INNER JOIN cte2 ON cte1.rn=cte2.rn AND cte1.col_name = cte2.col_name
    WHERE cte1.col_value <> cte2.col_value
    

**[Results][2]**:
    
    | RN | COLUMN NAME | TABLE1 VALUE | TABLE2 VALUE |
    |----|-------------|--------------|--------------|
    |  1 |        Col3 |   1 Stone St |  5 Pebble St |
    |  1 |        Col4 |      Bedrock |       Bedroc |



  [1]: http://sqlfiddle.com/#!6/6b8a1/1

Open in new window

{+ edit, sorry - typos}
0
 

Author Comment

by:SanPrg
ID: 39596266
Great,It's so quick.
Just I need to understand it, why  CROSS APPLY  ?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39596328
'Unpivoting' using CROSS APPLY
There are several methods for transposing columns to rows, this is one of them.

Personally I find the syntax very simple to understand, plus it is also very fast.
(In some testing done there is a suggestion it is faster than using unpivot, e.g:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/ )

So, it's a personal preference but one based on simplicity and speed.

------
I should note while I'm here that there might be occasions where my suggested solution might not work reliably. But I think this is is unavoidable.

The matching of the 2 results is achieved by row_number(), which rely on the order by that is used. e.g. order by col1,col2,col3,col4

If col1 is different between the 2 lists then row numbers may not align, and in fact this is true of any column:

so to mitigate use the columns with least difference at the start of that order by

The difficulty is that we can't include any reliably unique id in the except query (else everything is different) and because we don't have this reliable id we have to generate something, but because we have listed only differences that generated id may be faulty.

- a classic catch 22

{+ edit, corrected the URL }
0
 

Author Closing Comment

by:SanPrg
ID: 39598691
Excellent Work.
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question