Merge data where 1 column row the same-CodePudding

Current data:

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

Current Result:

Expected Result:

How to merge the data where TableDataOrder is the same?

CodePudding user response：

Try this:

select
    MasterID
    , TableDataOrder
    , max(Idea) Idea
    , max(Feasibility) Feasibility
    , max(BusinessReview) BusinessReview
    , max(Concept) Concept
    , max(DetailedDesign) DetailedDesign
    , max(TestValidation) TestValidation
    , max(ManufacturingProductIntegration) ManufacturingProductIntegration
    , max(ProductSales) ProductSales

from #Tmp

group by MasterID
    ,TableDataOrder;

It does not matter avg() or min() or max() or else. But pay attention, if Feasibility has been written twice with different value for same TableDataOrder it will not work.

CodePudding user response：

You don't want to insert a new row, so you cannot use INSERT, you have to use UPDATE instead. But since you do not know if the row will overwrite a new one, you can use ON DUPLICATE KEY UPDATE, like this :

INSERT INTO #Tmp (MasterID, TableDataOrder, Idea, Feasability, ...)
SELECT (26, 1, NULL, '02', NULL, NULL, NULL, NULL, NULL, NULL) AS masterid_new, ...
ON DUPLICATE KEY UPDATE
Idea = COALESCE(Idea, idea_new),
Feasability = COALESCE(Feasability, feasability_new)
...

The COALESCE function will return the first argument that is not NULL, so the existing value if there is one, otherwise the new one you provide. The statement also assumes that your TableDataOrder is declared a primary key for the ON DUPLICATE KEY UPDATE to work.