We can use a pivot query approach here. Assuming there would only be a maxmimum of 3 trip points:

SELECT
    [Trip Type],
    [Trip Name],
    MAX(CASE WHEN [Trip Loc seq] = 1
             THEN [Trip Point No] + '-' + [Trip Point Name] END) AS [Trip Point 01],
    MAX(CASE WHEN [Trip Loc seq] = 2
             THEN [Trip Point No] + '-' + [Trip Point Name] END) AS [Trip Point 02],
    MAX(CASE WHEN [Trip Loc seq] = 3
             THEN [Trip Point No] + '-' + [Trip Point Name] END) AS [Trip Point 03]
FROM yourTable
GROUP BY
    [Trip Type],
    [Trip Name];

data

CREATE TABLE TripPoints(
   Trip_Type       VARCHAR(100) NOT NULL 
  ,Trip_Name       VARCHAR(100) NOT NULL
  ,Trip_Loc_seq    INTEGER  NOT NULL
  ,Trip_Point_No   VARCHAR(100)  NOT NULL
  ,Trip_Point_Name VARCHAR(100) NOT NULL
);
INSERT INTO TripPoints
(Trip_Type,Trip_Name,Trip_Loc_seq,Trip_Point_No,Trip_Point_Name) VALUES 
('TRAVEL','N-ASIA',1,'0001','HK'),
('TRAVEL','N-ASIA',2,'0002','Japan'),
('TRAVEL','N-ASIA',3,'0003','South Korea'),
('TRAVEL','S-ASIA',1,'0004','Thailand'),
('TRAVEL','S-ASIA',2,'0005','Singapore'),
('BUSINESS','N-ASIA',1,'0001','HK'),
('BUSINESS','N-ASIA',2,'0006','TAIWAN');

use pivot and concat as follows

select *
from (
    select 
        Trip_Type,
        Trip_Name,
        concat('Trip Point 0',Trip_Loc_seq) name,
        concat(Trip_Point_No ,' ',Trip_Point_Name) value
    from TripPoints
) src
pivot (
  max(value)
  for name in ([Trip Point 01], [Trip Point 02], [Trip Point 03])
) piv;

dbfiddle

You need to prepare a dynamic query based on the MAX of Trip_Loc_seq.

Data Preperation:

CREATE TABLE TripPoints(
   Trip_Type       VARCHAR(100) NOT NULL 
  ,Trip_Name       VARCHAR(100) NOT NULL
  ,Trip_Loc_seq    INTEGER  NOT NULL
  ,Trip_Point_No   VARCHAR(100)  NOT NULL
  ,Trip_Point_Name VARCHAR(100) NOT NULL
);
INSERT INTO TripPoints
(Trip_Type,Trip_Name,Trip_Loc_seq,Trip_Point_No,Trip_Point_Name) VALUES 
('TRAVEL','N-ASIA',1,'0001','HK'),
('TRAVEL','N-ASIA',2,'0002','Japan'),
('TRAVEL','N-ASIA',3,'0003','South Korea'),
('TRAVEL','S-ASIA',1,'0004','Thailand'),
('TRAVEL','S-ASIA',2,'0005','Singapore'),
('BUSINESS','N-ASIA',1,'0001','HK'),
('BUSINESS','N-ASIA',2,'0006','TAIWAN');

Dynamic SQL Query:

DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX);

-- Determine the maximum Trip_Loc_seq value
DECLARE @MaxTripLocSeq INT;
SELECT @MaxTripLocSeq = MAX(Trip_Loc_seq)
FROM TripPoints;

-- Generate a comma-separated list of column names
SET @Columns = '';
DECLARE @Counter INT = 1;
WHILE @Counter <= @MaxTripLocSeq
BEGIN
    SET @Columns = @Columns + ', MAX(CASE WHEN Trip_Loc_seq = ' + CAST(@Counter AS NVARCHAR(10)) +
                   ' THEN Trip_Point_No+''-''+Trip_Point_Name END) AS Trip_Point_No_' + CAST(@Counter AS NVARCHAR(10));
    SET @Counter = @Counter + 1;
END;
SET @Columns = RIGHT(@Columns, LEN(@Columns) - 2);

-- Prepare the dynamic SQL query
SET @DynamicSQL = '
SELECT Trip_Type, Trip_Name, ' + @Columns + '
FROM TripPoints
GROUP BY Trip_Type, Trip_Name;';
print(@DynamicSQL)
-- Execute the dynamic SQL query
EXEC sp_executesql @DynamicSQL;