No Brief Description for This - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

No Brief Description for This

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-10-15 : 10:24:57

I have a view that compares values in 2 tables and indicates whther various fields match or not. The query follows this format:

SELECT Table1.A, Table2.A,

       CASE WHEN Table1.A = Table2.A
            THEN 'Match'
            ELSE 'No Match'
       END AS [A Compare],

       Table1.B, Table2.B,

       CASE WHEN Table1.B = Table2.B
            THEN 'Match'
            ELSE 'No Match'
       END AS [B Compare],

And so on for about 30 columns. Now the business users have asked for an additional column that lists which fields were "No Match" for each row. So if a row had "No Match" for columns A, B and F but matched on all others, then that new column needs to have "A, B, F" as the value for that row.

Since this could include anywhere from 1 to 30 columns, I need a way to concatenate all those "No Match" columns and insert it into that new column in the view. So far I'm having no luck. Any ideas appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-15 : 12:00:42

[code] CASE
WHEN Table1.A <> Table2.A
THEN 'A,'
ELSE ''
END +
CASE WHEN Table1.B <> Table2.B
THEN 'B,'
ELSE ''
END+
.....[/code]
and so on with a little additional logic to remove the trailing comma.

Note: If your columns are nullable, the check is more complex:
[code] WHEN (Table1.A <> Table2.A) or
(Table1.A is null and Table2.A is not null) or
(Table1.A is not null and Table2.A is null)[/code]

CODO ERGO SUM

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-10-15 : 13:24:45

That did it - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources