将具有相同 ID 的多行合并为一行 [英] Merge multiple rows with same ID into one row

问题描述

如何将具有相同 ID 的多行合并为一行.

How can I merge multiple rows with same ID into one row.

当同一列的第一行和第二行的值相同或第一行有值而第二行有NULL时.当同一列的第一行和第二行的值不同时，我不想合并.

When value in first and second row in the same column is the same or when there is value in first row and NULL in second row. I don't want to merge when value in first and second row in the same column is different.

我有桌子:

ID |A    |B    |C
1   NULL  31    NULL
1   412   NULL  1
2   567   38    4
2   567   NULL  NULL
3   2     NULL  NULL
3   5     NULL  NULL
4   6     1     NULL
4   8     NULL  5
4   NULL  NULL  5

我想要一张桌子:

ID |A    |B    |C
1   412   31    1
2   567   38    4
3   2     NULL  NULL
3   5     NULL  NULL
4   6     1     NULL
4   8     NULL  5
4   NULL  NULL  5

推荐答案

我认为上面的答案有一个更简单的解决方案(这也是正确的).它基本上获取可以在 CTE 内合并的合并值，然后将其与无法合并的数据合并.

I think there's a simpler solution to the above answers (which is also correct). It basically gets the merged values that can be merged within a CTE, then merges that with the data not able to be merged.

WITH CTE AS (
    SELECT
        ID,
        MAX(A) AS A,
        MAX(B) AS B,
        MAX(C) AS C
    FROM dbo.Records
    GROUP BY ID
    HAVING MAX(A) = MIN(A)
        AND MAX(B) = MIN(B)
        AND MAX(C) = MIN(C)
)
    SELECT *
    FROM CTE
    UNION ALL
    SELECT *
    FROM dbo.Records
    WHERE ID NOT IN (SELECT ID FROM CTE)

SQL 小提琴:http://www.sqlfiddle.com/#!6/29407/1/0

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