SQL Server:将多行数据合并为一行 [英] SQL Server : Merging Several Rows of Data into a Single Row

问题描述

我想要做的是从 Transact-SQL 或 SSIS 中合并几行数据以显示为一行.例如:

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:

制作:

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

进入这个:

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

所以我所做的是将值合并在一起，忽略空值.(D = 数据；T = 更新)

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

欢迎提出任何建议.

谢谢.

推荐答案

这会起作用，但由于没有标识或日期时间列 - 无法找到更新的更新行.因此，如果同一列有更多更新，我只会按字母/数字 (MIN) 取第一个.

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 演示

如果可以添加identity列，我们只需重写CTE部分，使其更准确.

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

如果我们有标识列，并且 CTE 被重写为递归，实际上可以删除查询的整个其他部分.

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 演示

这篇关于SQL Server:将多行数据合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！