SQL use select statement to add build a table – no insert op

Solution for SQL use select statement to add build a table – no insert op
is Given Below:

I need to use the select statement to form a table

DECLARE @MetresPerMile FLOAT = 1609.344;
DECLARE @LOCStart1 GEOGRAPHY = GEOGRAPHY::Point(48.83000,-97.31000,4326)
DECLARE @LOCDest1 GEOGRAPHY = GEOGRAPHY::Point(48.83000,-97.31000,4326)
DECLARE @LOCStart2 GEOGRAPHY = GEOGRAPHY::Point(22.9230000,-94.5342000,4326)
DECLARE @LOCDest2 GEOGRAPHY = GEOGRAPHY::Point(22.9230000,-94.5342000,4326)

SELECT 
    '1' [Start], '1' [Dest], 
    @LOCStart1.STDistance(@LOCDest1) / @MetresPerMile [Distance],
    '2' [Start], '2' [Dest],
    @LOCStart2.STDistance(@LOCDest1) / @MetresPerMile [Distance];

The result I’m getting is 6 columns, I just need 3 columns:

enter image description here

With kudos to Marc Guillot for his answer, does this get your desired result?

declare @MetresPerMile float = 1609.344;

with Points as (
  select *
  from (values
    (1, GEOGRAPHY::Point(48.83000,-97.31000,4326)),
    (2, GEOGRAPHY::Point(22.9230000,-94.5342000,4326))
  ) pts (Id, Point)
)
select Start.Id as Start, Dest.Id as Dest, 
       Start.Point.STDistance(Dest.Point)/@MetresPerMile as Distance
from Points as Start
cross join Points as Dest

This will give you that result :

declare @MetresPerMile float = 1609.344;
declare @Points table (Id integer, Point geography);

insert into @Points (Id, Point) 
       values (1, GEOGRAPHY::Point(48.83000,-97.31000,4326)),
              (2, GEOGRAPHY::Point(22.9230000,-94.5342000,4326));

select Start.Id as Start, Dest.Id as Dest, 
       Start.Point.STDistance(Dest.Point)/@MetresPerMile
from @Points as Start
     cross join @Points as Dest

You can add as many point as you need in the Points table, and the select will return the distances between all of them.

You can see it working here : Fiddle