CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlssis
Nitz
Nitz

Reputation: 33

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

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.

Upvotes: 3

Views: 11364

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 2

CrowbarKZ
CrowbarKZ

Reputation: 1243

You can try to do it with a cursor:

BEGIN
  declare @Title sometype, @Surname sometype, @Forename sometype, @DOB sometype, @Add1 sometype, @Postcode sometype --vars to fetch the crusor into
  declare @rTitle sometype, @rSurname sometype, @rForename sometype, @rDOB sometype, @rAdd1 sometype, @rPostcode sometype --vars to keep the result

  DECLARE mycur CURSOR FOR  
    SELECT Title,Surname,Forename,DOB,Add1,Postcode
      FROM t1
      WHERE where id = 10 --or some parameter if you have a procedure
      ORDER BY REF -- add another column here if you decide to create one (e.g. date_created)

  OPEN mycur   
  FETCH NEXT FROM mycur INTO @Title, @Surname, @Forename, @DOB, @Add1, @Postcode 

  WHILE @@FETCH_STATUS = 0   
  BEGIN   
    SET @rTitle = isnull(@Title,@rTitle) -- update the result with the new value unless the new one is null
    ... -- repeat for all the variables
  END   

  CLOSE mycur
  DEALLOCATE mycur

--here use all the result variables for whatever you wish
END

ORDER BY REF makes updates come after data, since basically T > D

Upvotes: 0

Related Questions