CODEWITHSUNDEEP
sqlsql-server
DarrenB
DarrenB

Reputation: 75

Merge data where 1 column row the same

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:

CurrentResult

Expected Result:

ExpectedResult

How to merge the data where TableDataOrder is the same?

Upvotes: 0

Views: 56

Answers (1)

RomanG
RomanG

Reputation: 264

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.

Upvotes: 2

Related Questions