# 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:

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