Compare Two Row in the table and find what columns are changed based on idno and date

Question

Compare Two Row in the table and find what columns are changed based on idno and date

TT_Turtle

SSC Rookie

Points: 28
More actions
March 2, 2019 at 12:11 am

#409562

f

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

I have to admit to being strongly confused because your test data doesn't match your desired output.

For example, here's your desired output... I've pointed to two rows for my example of confusion (with apologies for really messed up alignment that the forum software on this site causes)...
+-----------+---------+--------------+------------------+--------------+----------+ | reqid | Name | idno | colName | From_Value | To_Value | +-----------+---------+--------------+------------------+--------------+----------+ | 170916258 | TEST3_2 | 50505050505 | empname | | YOLO | | 170916258 | TEST3_2 | 50505050505 | emptyp | | 113 | | 170916258 | TEST3_2 | 50505050505 | gender | P | | | 170916258 | TEST3_2 | 50505050505 | income | 8891126 | | | 170916258 | TEST3_2 | 50505050505 | name | TEST2 | TEST3_2 | | 170916258 | EHH4_4 | 100202025698 | gender | L | P | <----This row | 170916258 | EHH4_4 | 100202025698 | name | APPLICANT5_2 | CUST3_6 | <----And this row | 170916258 | EHH4_4 | 260404045698 | gender | | P | | 170916258 | EHH4_4 | 260404045698 | name | CUST4_3 | EHH4_4 | +-----------+---------+--------------+------------------+--------------+----------+

Now, if you do the following SELECT...
SELECT * FROM #mydatabase WHERE IDNo = '100202025698' ORDER BY ID

You'll notice that there was an initial row followed by 3 change rows and NONE of them ever had the name of EHH4_4.

With that, you're going to have to be a whole lot more specific as to how the data you provided will result in the desired result please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TT_Turtle SSC Rookie Points: 28 More actions · Answer 2

updated the query trick as well. now i just want how to add column name in unpivot/pivot script. any idea how to do it? please note i have unpivot the column name. i just want an extra column show the updated result. sorry for confusing.