CODEWITHSUNDEEP
sqlsql-servert-sql
user441521
user441521

Reputation: 6988

Group by some fields and concat others

I'm comparing 2 tables. One has new data and the other has last months old data. The data comes monthly and it's all the data each time. There is no PK and any field can change. I need to show what fields and their old values for records that changed. It could be 1 or more fields. To do this I created inner joins where all fields are joined on except 1 and then in the where I say the old value is not equal to the new value and then I add 2 columns that tell me what field we are looking at and what the old value was. I do this query for each field and union the results so my final table looks like:

FieldA, FieldB, FieldC, FieldChanged, OldValue

FieldChanged will have field names that changed for that record and OldValue will say what the old value was.

If a record has more than 1 field changed (which there are) then I get that Field A-C duplicated in the result, with a different FieldChanged and OldValue values. However, I want to do a group by FieldA, FieldB, FieldC, and somehow contact FieldChanged from all the same records and OldValue from all the same records.

The union query result would look like (the first 3 fields is how the new data looks like):

"Tiger", "74", "Masters", "FieldC", "The Masters"
"Tiger", "74", "Masters", "FieldB", "68"

I would like to group by fields 1-3 and concat the last 2 fields in the group:

"Tiger", "74", "Masters", "FieldB,FieldC", "68,The Masters"

Now this is just an example so it's simple and might be easy to say this is a new record, however my table has 12 fields and so if everything matches but 1 field, chances are it's the same record and they just updated one of the fields.

The key here is being able to group by certain columns and concat other field values from the grouped columns together so that the same record isn't repeated and I have 1 line that shows every field that has changed for that record and it's old values.

Upvotes: 1

Views: 85

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

try this.

CREATE TABLE #test
  (
     FieldA       VARCHAR(25),
     FieldB       VARCHAR(25),
     FieldC       VARCHAR(25),
     FieldChanged VARCHAR(25),
     OldValue     VARCHAR(25)
  )

INSERT #test
VALUES ('Tiger','74','Masters','FieldC','The Masters'),
       ('Tiger','74','Masters','FieldB','68')

Method 1: For XML PATH

SELECT my_column AS [text()] 
FROM   my_table 
FOR XML PATH('')

This gives you the concatenated values of all of the values of this column in the table

SELECT FieldA,
       FieldB,
       FieldC,
       Stuff((SELECT ', ' + FieldChanged
              FROM   #test b
              WHERE  b.FieldA = a.FieldA
                     AND b.FieldB = a.FieldB
                     AND b.FieldC = a.FieldC
              FOR XML PATH('')), 1, 2, '') FieldChanged,
       Stuff((SELECT ', ' + OldValue
              FROM   #test b
              WHERE  b.FieldA = a.FieldA
                     AND b.FieldB = a.FieldB
                     AND b.FieldC = a.FieldC
              FOR XML PATH('')), 1, 2, '') OldValue
FROM   #test a
GROUP  BY FieldA,
          FieldB,
          FieldC

Method 2: Using Cross Apply

SELECT FieldA,
       FieldB,
       FieldC,
       Substring(d.FieldChanged, 1, Len(d.FieldChanged) - 1) FieldChanged,
       Substring(d.OldValue, 1, Len(d.OldValue) - 1)         OldValue
FROM   (SELECT DISTINCT FieldA,
                        FieldB,
                        FieldC
        FROM   #test) a
       CROSS APPLY (SELECT (SELECT FieldChanged + ', '
                            FROM   #test AS B
                            WHERE  b.FieldA = a.FieldA
                                   AND b.FieldB = a.FieldB
                                   AND b.FieldC = a.FieldC
                            FOR XML PATH('')),
                           (SELECT OldValue + ', '
                            FROM   #test AS B
                            WHERE  b.FieldA = a.FieldA
                                   AND b.FieldB = a.FieldB
                                   AND b.FieldC = a.FieldC
                            FOR XML PATH(''))) D (FieldChanged, OldValue)

Upvotes: 2

Related Questions