CODEWITHSUNDEEP
sqlsql-server
mathew paton
mathew paton

Reputation: 13

SQL Case Statement To Evaluate All Conditions

So I have an interesting issue that I am trying to solve and think you can help.

I have a table with about 100 columns and about 1 million records. I will receive weekly a new iteration of the table -What I would like to do is evaluate what data has changed by column from iteration to iteration of the table. note that when i get a new table, all column names will bet the same but data could (and will) be different. My goal is to create a table of counts of all the columns that changed from iteration to iteration by unique record (join on newtable.id = oldtable.id).

My issue is my case statement. Is there a way to get the case statement to evaluate all your when statements and execute all actions before it exits? See code below- How do i get this case statement to insert both changed values (aka I want to see TEST_A and TEST_B in the output under ColumnTEST.) I understand that I will have to make this dynamic and make a cursor loop for it (considering the massive amount of overhead cursors take, an alternate suggestion would be welcomed) but my issue now lies in the case statement- please help me avoid a massive union statement...

Thank you!!

CREATE TABLE #TEST1 (TEST VARCHAR(100), COLUMNTEST VARCHAR(100), id varchar(10))
CREATE TABLE #TEST2 (TEST VARCHAR(100), COLUMNTEST VARCHAR(100),id varchar(10))
CREATE TABLE #TEST3 (COLUMNTEST VARCHAR(100))

INSERT INTO #TEST1(TEST, COLUMNTEST, ID) VALUES ('100','90','1')
INSERT INTO #TEST2(TEST, COLUMNTEST, ID) VALUES ('1000','900','1')

INSERT INTO #TEST3 (COLUMNTEST)
SELECT CASE 
WHEN  #TEST1.COLUMNTEST <> #TEST2.COLUMNTEST THEN 'TEST_A' 
WHEN #TEST1.TEST <> #TEST2.TEST THEN 'TEST_B' END 

FROM #TEST1 
join #test2 
    ON #TEST1.ID = #TEST2.ID

SELECT * FROM #TEST3

DROP TABLE #TEST1
DROP TABLE #TEST2
DROP TABLE #TEST3

Upvotes: 0

Views: 1993

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You can do that by concatenate the strings and use STRING_SPLIT() function as

INSERT INTO #TEST3 (COLUMNTEST)
SELECT *
FROM STRING_SPLIT((
SELECT CASE 
       WHEN  (#TEST1.COLUMNTEST <> #TEST2.COLUMNTEST AND #TEST1.TEST <> #TEST2.TEST) 
       THEN 'TEST_A,TEST_B'
       WHEN (#TEST1.TEST <> #TEST2.TEST) THEN 'TEST_B' 
       WHEN (#TEST1.COLUMNTEST <> #TEST2.COLUMNTEST) THEN 'TEST_A'
       END
FROM #TEST1 
JOIN #test2 
    ON #TEST1.ID = #TEST2.ID),',')

Demo

Note that STRING_SPLIT() function is only avaliable on 2016+ versions, so you can create yours if you don't have SQL Server 2016 OR 2017.

Upvotes: 1

Related Questions