T-SQL - Compare multiple Columns in the same row

Question

I need to compare multiple columns from then same row in a table For example I have on a row

---

1 | name | surname | phone | nameWeb | surnameWeb | phoneWeb ...

---

and I need to compare data from DB and data from Web [....Web] columns something like this

---

name | nameWeb

---

surname | surnameWeb

---

phone | phoneWeb ...

---

I did this with temporary tables and multiple inserts but I need an optimized solusition because I have a lot of columns

my code

SELECT    
 -- Contatto
                c.id , 
 -- Ditta
                d.nome 'nomeDitta' ,
                d.filiale ,
                d.webNome webNomeDitta ,
                d.webDescrizione webDescrizione ,
-- Persona  
                p.nome Nome ,
                p.cognome Cognome ,
                p.email ,
                p.telefono ,
                p.fax ,
                p.webNome ,
                p.webCognome ,
                p.webEmail ,
                p.webTelefono ,
                p.webFax ,
                p.webNoteAggiuntive ,
                p.canali ,
-- Indirizzo
                i.indirizzo1 ,
                i.indirizzo2 ,
                i.cap ,
                i.localita ,
                i.webIndirizzo1 ,
                i.webIndirizzo2 ,
                i.webCap ,
                i.webLocalita ,
                i.webNome 'NomeInd' ,
-- Nazione
                n.stato 'Nazione' ,
                n2.stato 'webNazione' ,
-- Lingua
                L.nome 'webLingua'
        INTO    #webCont
        FROM    dbo.contatto c ...
        WHERE   c.id = @idContatto

        DECLARE @result TABLE ( ColumnName  NVARCHAR(100) ,
                                DB          NVARCHAR(100) ,
                                Web         NVARCHAR(100) ,
                                hasData     BIT)

        INSERT  INTO @result    SELECT  'Nome Ditta' , nomeDitta , webNomeDitta , @hasData   FROM    #webCont
        INSERT  INTO @result    SELECT  'Nome' , Nome , webNome , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Cognome' , Cognome , webCognome , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'eMail' , email , webEmail , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Telefono' , telefono , webTelefono , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Fax' , fax , webFax , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Indirizzo 1' , indirizzo1 , webIndirizzo1 , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Indirizzo 2' , indirizzo2 , webIndirizzo2 , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Cap' , cap , webCap , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Localita' , localita , webLocalita , @hasData    FROM    #webCont
        INSERT  INTO @result    SELECT  'Nazione' , Nazione , webNazione , @hasData    FROM    #webCont

Thank you , Marian

Answer 1

I think you don't need @result table, you can use UNION instead. Also you should do analysis of your query we don't see in your sample FROM dbo.contatto c ... Here described how to do this. Might be you also don't need #webCont, but it really depend. Might be you should consider using Indexed Views, they slows operations but can be used for lookup tables.

Answer 2

Ok, I tried to compile a fairly detailed example for you. What this will do, is build up a dynamic query by:

1. Finding all columns in the specific table NOT ending in "Web"
2. Finding all columns in the specific table that DO end in "Web"
3. Join those two result-sets together and construct a test to measure equality
4. Use the combined result-set to construct a dynamic query that will execute and perform the necessary matches across all tables that might have a "%Web" counterpart.

Obviously, this solution won't work 100% for you, but might guide you into the solution you need. So, without any further ado, I present THE CODE:

DECLARE 
    @SQL nvarchar(max)
  , @TableName nvarchar(max)

SET @TableName = 'Test'

SET @SQL = 'SELECT '
;WITH NonWeb AS
(
SELECT
    [COLUMN_NAME] as [NonWebColumn]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    [TABLE_NAME] = @TableName
AND [COLUMN_NAME] NOT LIKE '%Web'
),
Web AS
(
SELECT
    '[' + C.[COLUMN_NAME] + ']' as [WebColumn]
  , '[' + NonWeb.[NonWebColumn] + ']' as [NonWebColumn]
  , 'CASE WHEN [' + C.[COLUMN_NAME] + '] = [' + NonWeb.[NonWebColumn] + '] THEN ''Equal'' ELSE ''Not Equal'' END as [' + NonWeb.[NonWebColumn] + 'Match]' as [Match]
FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN NonWeb ON
        REPLACE(C.[COLUMN_NAME],'Web','') = NonWeb.[NonWebColumn]
WHERE 
    [TABLE_NAME] = @TableName
AND [COLUMN_NAME] LIKE '%Web'
)

SELECT @SQL = 'SELECT ' + STUFF
(
    (
        SELECT
            ', ' + [NonWebColumn] + ', ' + [WebColumn] + ', ' + [Match]
        FROM Web
        FOR XML PATH('')
    )
  , 1
  , 2
  , ''
) + ' FROM ' + @TableName

EXEC sp_ExecuteSql @SQL

You can just change the value of the @TableName variable (which gets set at the beginning) to whatever the name of your specific table is and test it out. The assumption here is of course that there are columns that end in "Web" in that specific table...