Query to Compare Values in a Table Against Each other Based on the Values in a Specific Column and Show Only Differencs Ignoring Nulls

Question

I have a table with data showing different codes and descriptions used at different locations. I need to combine these locations into one. Before I can do that, I need to be able to clearly see two sets of data, the different descriptions used for a code in a given category at a given location, and the different codes used for a description in a given category at a given location, so I can get the data to agree. While a given code will only have one description per category per location, a given description can have multiple codes per category per location. I am only concerned with the cases where the codes and categories are used differently at the various locations. For these purposes a code or description that is the same at one or more locations, but is not used at the others is not different.

I need to be able to run one query that will show us, for a given category and code, all of the descriptions by location, including nulls where that code is not used, where the descriptions are not the same across all locations. Again, if the code has no differences other than null, it should not be on the list.

I need to be able to run another query that will show us, for a given category and description, ALL of the codes by location, including nulls where that description is not used, where the codes are not the same across all locations, keeping in mind that there may be more than one code per location. Again as above, if the codes are the same for a given category and description, but isn't used at all locations, it should not be returned.

So, if my table has:

LOCATION    CATEGORY    CODE    DESCR
aaa         Dept        001     Pharmacy
bbb         Dept        001     Pharmacy
ccc         Dept        002     Pharmacy
ddd         Dept        002     Labratory
aaa         Dept        003     Clerical
bbb         Dept        003     Laundry
ccc         Dept        003     IT
ddd         Dept        003     Accounting
aaa         Dept        004     Purchasing
bbb         Dept        004     Purchasing
ccc         Dept        004     Purchasing
ddd         Dept        004     Purchasing
aaa         Job         004     Recepionist
bbb         Job         004     Recepionist
bbb         Job         104     Recepionist
ccc         Job         004     Recepionist
ddd         Job         004     Recepionist

Same Code Different Descriptions

CATEGORY    CODE    aaa_DESCR   bbb_DESCR   ccc_DESCR   ddd_DESCR
Dept        002     NULL        NULL        Pharmacy    Labratory
Dept        003     Clerical    Laundry     IT          Accounting

Same Description Different Codes

CATEGORY    DESCR       aaa_CODE1   bbb_CODE1   bbb_CODE2   ccc_CODE1   ddd_CODE1
Dept        Pharmacy    001         001         NULL        002         NULL
Job         Recepionist 004         004         104         004         004

If having multiple columns where there are multiple codes for the same description at a given location isn't possible, then perhaps it would be possible to put all the codes for that location in one column seperated by a specificed character, like this:

CATEGORY    DESCR       aaa_CODE    bbb_CODE    ccc_CODE    ddd_CODE
Dept        Pharmacy    001         001         002         NULL
Job         Recepionist 004         004|104     004         004

Can you post ddl and sample data? sqlfiddle.com would be a great place to start. — Sean Lange, May 26 '15 at 14:44
Looks like something that could be done with CASE statements. Are you familiar with how they work? — Tab Alleman, May 26 '15 at 15:06

score 2 · Accepted Answer · edited May 23 '17 at 12:30

The first step you need is to identify the rows you need to format, so for your first criterion where you want to find where the same code and category have more than one description, I would use EXISTS:

SELECT  t.Location, t.Category, t.Code, t.Descr
FROM    #T AS t
WHERE   EXISTS
        (   SELECT  1
            FROM    #T AS t2
            WHERE   t2.Category = t.Category
            AND     t2.Code = t.Code
            AND     t2.Descr != t.Descr
        );

This gives the rows we need to format:

Location    Category    Code    Descr
-------------------------------------------
ccc         Dept        002     Pharmacy
ddd         Dept        002     Labratory
aaa         Dept        003     Clerical
bbb         Dept        003     Laundry
ccc         Dept        003     IT
ddd         Dept        003     Accounting

Then, if you have a unique Desc per category, code and location, as we do here you can simply use an arbitrary aggregate inside the PIVOT function to get your desired format:

SELECT  pvt.Category,
        pvt.[aaa], 
        pvt.[bbb], 
        pvt.[ccc], 
        pvt.[ddd]
FROM    (   SELECT  t.Location, t.Category, t.Code, t.Descr
            FROM    #T AS t
            WHERE   EXISTS
                    (   SELECT  1
                        FROM    #T AS t2
                        WHERE   t2.Category = t.Category
                        AND     t2.Code = t.Code
                        AND     t2.Descr != t.Descr
                    )
        ) AS t
        PIVOT
        (   MAX(Descr)
            FOR Location IN ([aaa], [bbb], [ccc], [ddd])
        ) AS pvt;

It gets more complicated when you might have more than one value, so you can't simply use MAX inside PIVOT. Here, you will need to use SQL Server's XML extensions to concatenate your similar rows into a single row.

The following uses the same EXISTS logic to find the correct rows, but then rather than just selecting the Code uses a subuqery to collect all the codes for that particular combination of (Category, Location, Descr) and combines them into a single record:

SELECT  t.Location, 
        t.Category, 
        t.Descr,
        Code = STUFF((  SELECT '|' + t2.Code
                        FROM    #T AS t2
                        WHERE   t2.Category = t.Category
                        AND     t2.Descr = t.Descr
                        AND     t2.Location = t.Location
                        FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM    #T AS t
WHERE   EXISTS
        (   SELECT  1
            FROM    #T AS t2
            WHERE   t2.Category = t.Category
            AND     t2.Descr = t.Descr
            AND     t2.Code != t.Code
        )
GROUP BY t.Location, t.Category, t.Descr;

This gives:

Location    Category    Descr       Code
--------------------------------------------
aaa         Dept        Pharmacy    001
aaa         Job         Recepionist 004
bbb         Dept        Pharmacy    001
bbb         Job         Recepionist 004|104
ccc         Dept        Pharmacy    002
ccc         Job         Recepionist 004
ddd         Job         Recepionist 004

Once you have this format, you can again use PIVOT to get your desired output.

SELECT  pvt.Category,
        pvt.[aaa], 
        pvt.[bbb], 
        pvt.[ccc], 
        pvt.[ddd]
FROM    (   SELECT  t.Location, 
                    t.Category, 
                    t.Descr,
                    Code = STUFF((  SELECT '|' + t2.Code
                                    FROM    #T AS t2
                                    WHERE   t2.Category = t.Category
                                    AND     t2.Descr = t.Descr
                                    AND     t2.Location = t.Location
                                    FOR XML PATH(''), TYPE
                                    ).value('.', 'VARCHAR(MAX)'), 1, 1, '')
            FROM    #T AS t
            WHERE   EXISTS
                    (   SELECT  1
                        FROM    #T AS t2
                        WHERE   t2.Category = t.Category
                        AND     t2.Descr = t.Descr
                        AND     t2.Code != t.Code
                    )
            GROUP BY t.Location, t.Category, t.Descr
        ) AS t
        PIVOT
        (   MAX(Code)
            FOR Location IN ([aaa], [bbb], [ccc], [ddd])
        ) AS pvt;

Examples of Both Queries on SQL Fiddle

For completeness below is the table I used for testing:

CREATE TABLE #T (Location VARCHAR(3), Category VARCHAR(4), Code CHAR(3), Descr VARCHAR(50));

INSERT INTO #T (Location, Category, Code, Descr)
VALUES
    ('aaa', 'Dept', '001', 'Pharmacy'),
    ('bbb', 'Dept', '001', 'Pharmacy'),
    ('ccc', 'Dept', '002', 'Pharmacy'),
    ('ddd', 'Dept', '002', 'Labratory'),
    ('aaa', 'Dept', '003', 'Clerical'),
    ('bbb', 'Dept', '003', 'Laundry'),
    ('ccc', 'Dept', '003', 'IT'),
    ('ddd', 'Dept', '003', 'Accounting'),
    ('aaa', 'Dept', '004', 'Purchasing'),
    ('bbb', 'Dept', '004', 'Purchasing'),
    ('ccc', 'Dept', '004', 'Purchasing'),
    ('ddd', 'Dept', '004', 'Purchasing'),
    ('aaa', 'Job', '004', 'Recepionist'),
    ('bbb', 'Job', '004', 'Recepionist'),
    ('bbb', 'Job', '104', 'Recepionist'),
    ('ccc', 'Job', '004', 'Recepionist'),
    ('ddd', 'Job', '004', 'Recepionist');

This is perfect. Thanks very much, both for the query and the explanations. One question, if this is possible, great, if not, it's great as is, and I don't want to waste your time. In second case, finding codes at a given location for a category and description, the query will always return codes if more than one code is used at a given location, even if those codes are only used at one location, or the same group of codes is used at more than one location, so, in a sense, it's not different. What modification would you have to make to only show where different codes are used? Thank you. — THEMEDIA, May 29 '15 at 18:49

Query to Compare Values in a Table Against Each other Based on the Values in a Specific Column and Show Only Differencs Ignoring Nulls

1 Answers1