question

LearningToCode avatar image
LearningToCode asked

TSQL - How to return query results over two rows

I'm trying to write a SQL query that will compare the same data in two tables, one is the original data and another will be an updated version of this data. I want to compare the two datasets for any changes and return those changes. That I have managed to do, but I'd like to return the results in different rows not in the same row. So currently my result set looks like this:

results1.png

I can see that it returns two Staff members who have changes in their details. Joe Smyth's name was wrong and has been corrected to Joe Smith and Sally Green has changed from Ms to Mrs. However, I'd like my returned dataset to be in this shape:

results2.png

This is the query I used to put it this way:

First I returned any changes to a temp table.
Then pulled the data I wanted from the different data sets using UNION to join them:

    SELECT      b.StaffNumber
    INTO        #StaffNumbers
    FROM        OriginalData a
    LEFT JOIN   NewData b
    ON          a.StaffNumber = b.StaffNumber
    AND         (a.Name != b.Name
    OR          a.Title != b.Title)
    WHERE       b.StaffNumber IS NOT NULL    

    SELECT  *
    FROM    (
                SELECT      'UPDATE' as [Type Of Change],
                            'Original Data' as [Data Source],
                            StaffNumber as [Staff Number],
                            Name,
                            Title
                FROM        App_Person a
                INNER JOIN  #GmcNumber b
                ON          a.StaffNumber = b.StaffNumber
                UNION
                SELECT      'UPDATE' as [Type Of Change],
                            'New Data' as [Data Source],
                            a.StaffNumber as [Staff Number],
                            Name,
                            Title,
                FROM        PersonExtract a
                INNER JOIN  #StaffNumbers b
                ON          a.StaffNumber = b.StaffNumber
            ) sub1
    ORDER BY [Staff Number]

So while I'm getting the result I want, I'm wondering if there's a smarter way to do this than what I've done above. If anybody has any ideas/suggestions.

Many thanks!!

t-sqltsql
results1.png (7.5 KiB)
results2.png (9.1 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.