SQL Server如何合并TableDataOrder列值相同的行数据

需求说明 #

按TableDataOrder字段对数据分组，将同组下各列的非空值聚合为单条记录。

测试数据脚本 #

IF OBJECT_ID(N'tempdb..#Tmp') IS NOT NULL
BEGIN
DROP TABLE #Tmp
END
create table #Tmp (MasterID int,TableDataOrder int,Idea varchar(50),Feasibility varchar(50),BusinessReview varchar(50)
                    ,Concept varchar(50),DetailedDesign varchar(50),TestValidation varchar(50),ManufacturingProductIntegration varchar(50)
                    ,ProductSales varchar(50))
insert into #Tmp
select 26,1,NULL,'02',NULL,NULL,NULL,NULL,NULL,NULL
insert into #Tmp
select 26,1,NULL,NULL,NULL,'04',NULL,NULL,NULL,NULL
insert into #Tmp
select 26,1,NULL,NULL,NULL,NULL,'05',NULL,NULL,NULL
insert into #Tmp
select 26,2,NULL,NULL,NULL,NULL,'05',NULL,NULL,NULL
insert into #Tmp
select 26,3,NULL,NULL,NULL,NULL,'05',NULL,NULL,NULL
insert into #Tmp
select 26,4,NULL,NULL,NULL,NULL,'05',NULL,NULL,NULL

select * from  #Tmp

当前查询结果 #

期望结果 #

实现方法 #

针对当前数据特征（同一分组下每个业务字段最多存在1个非空值，其余均为NULL），直接用MAX()/MIN()聚合函数即可，这类聚合函数会自动跳过NULL值，返回组内唯一的非空值：

SELECT
    MasterID,
    TableDataOrder,
    MAX(Idea) AS Idea,
    MAX(Feasibility) AS Feasibility,
    MAX(BusinessReview) AS BusinessReview,
    MAX(Concept) AS Concept,
    MAX(DetailedDesign) AS DetailedDesign,
    MAX(TestValidation) AS TestValidation,
    MAX(ManufacturingProductIntegration) AS ManufacturingProductIntegration,
    MAX(ProductSales) AS ProductSales
FROM #Tmp
-- 按MasterID和TableDataOrder共同分组，避免不同MasterID下相同TableDataOrder的数据被错误合并
GROUP BY MasterID, TableDataOrder
ORDER BY TableDataOrder

注意：如果后续业务调整，出现同一分组下同个字段存在多个不同非空值的场景，需要先明确多值的拼接规则，再替换为STRING_AGG()等字符串聚合函数实现。

内容的提问来源于stack exchange，提问作者DarrenB