How to conveniently compare values ​​in one table in TSQL

Question

I have a settings table built with a "category" that defines different products, "memory_name" is the name of the variable, and "information" is the value of the parameter.

for example,

select top 6 category, varname, info 
from settings_table 
where NODE_NAME='HTT-COMM-A' 
  and section='Module Settings' 
  and category in  ('ProductA', 'ProductB') 
order by varname

leads to:

 category varname info  
 ProductB WEB_ACCESS_ALLOW NO  
 ProductA WEB_ACCESS_ALLOW NO  
 ProductB WEB_ACCESS_BLOCK YES  
 ProductA WEB_ACCESS_BLOCK YES  
 ProductB WEB_ACCOUNT_DETAIL NO  
 ProductA WEB_ACCOUNT_DETAIL YES  

I would like to create a simple list of differences between the values ​​when category = 'ProductA' and 'ProductB'. I can come up with several ways to do this using a temporary table or several subqueries (for example, this is painful):

select a.category, a.varname, a.info , b.category, b.info 
from (select category, varname, info, description
      from settings_table 
      where category = 'ProductA') as a,
     (select category, varname,info, description 
      from settings_table 
      where category = 'ProductB') as b 
where a.varname=b.varname and a.info != b.info

( ) , b var, a. ( , a b .)

, , " " , .

SQL, MSSQL.

, Rk

Answer 1

varname info, - :

Select varname, info
From @Data As T
Except  (
        Select varname, info
        From @Data As T1
        Where category = 'ProductA'
        Intersect
        Select varname, info
        From @Data As T2
        Where category = 'ProductB'
        )

, - :

Select T.*
From settings_table As T
    Left Join   (
                Select T1.varname, T1.info
                From settings_table As T1
                Where T1.category = 'ProductA'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                Intersect
                Select T2.varname, T2.info
                From settings_table As T2
                Where T1.category = 'ProductB'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                ) As Z
        On Z.varname = T.varname
            And Z.info = T.info
Where Z.varname Is Null
    And T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'

EXISTS:

Select T.*
From settings_table As T
Where T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'
    And Not Exists  (
                    Select 1
                    From settings_table As T2
                    Where T2.category In('ProductA','ProductB')
                        And T2.varname = T.varname
                        And T2.info = T.info
                    Group By T2.varname, T2.info
                    Having Count(*) = 2
                    )

Answer 2

, :

select a.varname as varname,
a.info as 'ProductA_Setting',
b.info as 'ProductB_Setting'
from @t a
inner join @t b
on a.varname = b.varname
where a.category = 'ProductA'
and b.category = 'ProductB'
and a.info <> b.info

script :

declare @t table (category varchar(32), varname varchar(32), info varchar(32))

insert into @t select 'ProductB', 'WEB_ACCESS_ALLOW', 'NO'
insert into @t select 'ProductA', 'WEB_ACCESS_ALLOW', 'NO'
insert into @t select 'ProductB', 'WEB_ACCESS_BLOCK', 'YES'
insert into @t select 'ProductA', 'WEB_ACCESS_BLOCK', 'YES'
insert into @t select 'ProductB', 'WEB_ACCOUNT_DETAIL', 'NO'
insert into @t select 'ProductA', 'WEB_ACCOUNT_DETAIL', 'YES'

select * from @t

select a.varname as varname,
a.info as 'ProductA_Setting',
b.info as 'ProductB_Setting'
from @t a
inner join @t b
on a.varname = b.varname
where a.category = 'ProductA'
and b.category = 'ProductB'
and a.info <> b.info

Answer 3

CTE , :

WITH SETTINGS (category, varname, info)
AS
(
    SELECT category, varname, info
    FROM settings_table
    WHERE NODE_NAME = 'HTT-COMM-A'
        AND [section] = 'Module Settings'
        AND category IN ('ProductA', 'ProductB')
)
SELECT
    COALESCE(A.varname, B.varname) AS varname,
    A.info AS info_a,
    B.info AS info_b
FROM
    SETTINGS A
    FULL OUTER JOIN SETTINGS B
        ON A.category = 'ProductA'
            AND B.category = 'ProductB'
            AND A.varname = B.varname
WHERE
    A.varname IS NULL
    OR B.varname IS NULL    
    OR A.info!= B.info
ORDER BY
    COALESCE(A.varname, B.varname)

Answer 4

SELECT... EXCEPT SELECT... INTERSECT , , , , - .

temp Paul Kearney - pk :

DECLARE
  @Category1 varchar(32)
 ,@Category2 varchar(32)

SET @Category1 = 'ProductA'
SET @Category2 = 'ProductB'

SELECT isnull(set1.varname, set2.varname) varname, set1.Category, set1.Info, set2.Category, set2.Info
 from (--  Exists for "1" but not for "2"
       select @Category1 Category, varname, info
        from @t
        where category = @Category1
       except select @Category1, varname, info
        from @t
        where category = @Category2) set1
  full outer join (--  Exists for "2" but not for "1"
                   select @Category2 Category, varname, info
                     from @t
                     where category = @Category2
                    except select @Category2, varname, info
                     from @t
                     where category = @Category1) set2
   on set2.varname = set1.varname

, NULL Category Info.

Answer 5

-. ... promises . ", DDL!" , . , , . , ?

- , EAV, ?

, , , Fil'a'''fish...

Google = > SQL JOIN TYPES

, Outer Full Outer