SQL Server : Merging Several Rows of Data into a Single Row

Question

What I am looking to do is merge several rows of data to be displayed as a single row from within either Transact-SQL or SSIS. so for example:

MAKE:

REF  ID   Title Surname     Forename    DOB          Add1            Postcode
------------------------------------------------------------------------------------------    
D    10   MR    KINGSTON    NULL        15/07/1975   3 WATER SQUARE  NULL
T    10   NULL  NULL        BOB         NULL         NULL            NULL
T    10   MRS   NULL        NULL        NULL         NULL            TW13 7DT

into this:

REF  ID   Title Surname    Forename   DOB          Add1            Postcode
----------------------------------------------------------------------------------    
D    10   MRS   KINGSTON   BOB        15/07/1975   3 WATER SQUARE  TW13 7DT

So what I have done is merged the value together ignoring values that are null. (D = Data; T = Update)

Any suggestions would be most welcome.

Thanks.

Solution 1

This will work, but since there is no identity or datetime column - there is no way to find which update row is newer. So if there are more updates on the same column, I just take the first alphabetically/numerically (MIN).

WITH CTE AS 
(
    SELECT ID, REF, MIN(Title) Title, MIN(Surname) Surname, MIN(Forename) Forename, MIN(DOB) DOB, MIN(Add1) Add1, MIN(Postcode) Postcode
    FROM Table1
    GROUP BY id, REF
)
SELECT 
    d.REF
  , d.ID
  , COALESCE(T.Title, d.TItle) AS Title
  , COALESCE(T.Surname, d.Surname) AS Surname
  , COALESCE(T.Forename, d.Forename) AS Forename
  , COALESCE(T.DOB, d.DOB) AS DOB
  , COALESCE(T.Add1, d.Add1) AS Add1
  , COALESCE(T.Postcode, d.Postcode) AS Postcode
FROM CTE d 
INNER JOIN CTE t ON d.ID = t.ID AND d.REF = 'D' AND t.REF = 't'

SQLFiddle DEMO

If identity column can be added, we can just rewrite the CTE part to make it more accurate.

EDIT:

If we have identity column, and CTE is rewritten to become recursive, actually whole other part of query can be dropped.

WITH CTE_RN AS 
(
    --Assigning row_Numbers based on identity - it has to be done since identity can always have gaps which would break the recursion
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY IDNT DESC) RN FROM dbo.Table2
)
,RCTE AS 
(
    SELECT  ID ,
            Title ,
            Surname ,
            Forename ,
            DOB ,
            Add1 ,
            Postcode ,
            RN FROM CTE_RN WHERE RN = 1 -- taking the last row for each ID
    UNION ALL
    SELECT r.ID,
        COALESCE(r.TItle,p.TItle), --Coalesce will hold prev value if exist or use next one
        COALESCE(r.Surname,p.Surname),
        COALESCE(r.Forename,p.Forename),
        COALESCE(r.DOB,p.DOB),
        COALESCE(r.Add1,p.Add1),
        COALESCE(r.Postcode,p.Postcode),
        p.RN
    FROM RCTE r
    INNER JOIN CTE_RN p ON r.ID = p.ID AND r.RN + 1 = p.RN --joining the previous row for each id
)
,CTE_Group AS 
(
    --rcte now holds both merged and unmerged rows, merged is max(rn)
    SELECT ID, MAX(RN) RN FROM RCTE
    GROUP BY ID  
)
SELECT r.* FROM RCTE r
INNER JOIN CTE_Group g ON r.ID = g.ID AND r.RN = g.RN

SQLFiddle DEMO

Solution 2

I added an identity column id2 to make the logic work.

declare @t table(id2 int identity(1,1), 
REF char(1),
ID int,
Title varchar(10),
Surname varchar(10),
Forename varchar(10),
DOB date, 
Add1 varchar(15),
Postcode varchar(10)
)

insert @t values

('D',10, 'MR', 'KINGSTON', NULL, '19750715', '3 WATER SQUARE', NULL),
('T',10, NULL, NULL, 'BOB', NULL, NULL, NULL),
('T',10, 'MRS', NULL, NULL, NULL, NULL, 'TW13')

select Ref, t2.Title, t3.Surname, t4.Forename, t5.Dob, t6.Add1, t7.PostCode from @t t1
outer apply (select top 1 Title from @t where t1.id = id and Title is not null
order by id2 desc) t2
outer apply (select top 1 Surname from @t where t1.id = id and Surname is not null
order by id2 desc) t3
outer apply (select top 1 Forename from @t where t1.id = id and Forename is not null
order by id2 desc) t4
outer apply (select top 1 DOB from @t where t1.id = id and DOB is not null
order by id2 desc) t5
outer apply (select top 1 add1 from @t where t1.id = id and add1 is not null
order by id2 desc) t6
outer apply (select top 1 postcode from @t where t1.id = id and postcode is not null
order by id2 desc) t7
where Ref = 'D'

Result:

Ref Title  Surname  Forename  Dob         Add1            PostCode
D   MRS    KINGSTON BOB       1975-07-15  3 WATER SQUARE  TW13

Comments

[Dinup Kandel]

while merging what do you give the prority? in title you have chosen of MRS insead of MR .

[t-clausen.dk]

how do you determine which transaction is the second ? Please don't tell me that is it because it is the bottom one, you need a column like timestamp or id to 'order by' to describe which one it is

[t-clausen.dk]

@Nitz don't forget to accept the answer. Just be aware that this will not always work if a column change value more than once

[Nitz]

If I had an identity column on the main table how would the CTE look?

[Nenad Zivkovic]

@Nitz: I've edited the answer to include that solution. After starting to rewrite the CTE - at the end it turned to be almost completely different solution. There was no longer need to join D and T - as I guess T will always have newer IDNT then D.

[aganm]

Is there a way to do this for all of the fields without typing them out? Like SELECT * UNION ALL SELECT r.ID COALESCE *