CODEWITHSUNDEEP
sqlsql-serverconcatenation
KamyaSri
KamyaSri

Reputation: 13

Merge rows based on values in 2 tables

I have a table with travel details. Details are getting saved in distributed manner. I need to merge the rows based on Source and Destination. My Source is A and Final Destination is D, I need to merge all the 3 rows into 1 with sum of time and distance.

Table1:Trip details

CarID Source Destination Distance Time
1 A B 10 1
1 B C 20 2
1 C D 30 3

Table2: TravelPlan

CarID Source Destination
1 A D

Output Needed:
Table 3:

CarID Source Destination Distance Time
1 A D 60 6

I tried using Concatenate but not able to do based on conditions. Not sure how to combine rows of one table based on values of another.

Upvotes: 1

Views: 68

Answers (1)

RF1991
RF1991

Reputation: 2265

your Data

DECLARE @TripDetails TABLE (
   CarID       INT  NOT NULL 
  ,Source      VARCHAR(20) NOT NULL
  ,Destination VARCHAR(20) NOT NULL
  ,Distance    INT  NOT NULL
  ,Time        INT  NOT NULL
);
INSERT INTO @TripDetails
(CarID,Source,Destination,Distance,Time)
 VALUES 
(1,'A','B',10,1),
(1,'B','C',20,2),
(1,'C','D',30,3);


DECLARE @TravelPlan TABLE (
   CarID       INT  NOT NULL 
  ,Source      VARCHAR(20) NOT NULL
  ,Destination VARCHAR(20) NOT NULL
);
INSERT INTO @TravelPlan
(CarID,Source,Destination) VALUES 
(1,'A','D');

what you need are Subquery and join and SUM. your query

SELECT TP.carID,
       TP.Source,
       TP.Destination,
       TD.Distance,
       TD.Time
FROM   (select carID,
               Sum(Distance) Distance,
               Sum(Time)     Time
        FROM   @TripDetails
        GROUP BY carID) TD
       JOIN @TravelPlan TP
         ON TD.carID = TP.carID

Fiddle

Upvotes: 2

Related Questions