Selecting from multiple tables and distance calculation

Transact-SQL
1

I have two tables that I some how need to join and calculate the distance between each user across each event.
The output needs to look like a row of users and the distance between their long and lat and the venue long and lat. A problem I also have, is the number of venues available could be different for each event.

people
name
event id
long
lat

venue
name
eventid
long
lat

The output I am trying to achieve is,
name venue1 venue2 venue3
Bill       120       140        6
james    20        48        92
John      44        62       103

I have absolutely no idea how to build an output like this, any help would be greatly appreciated.

2

You can do a cross join on the two tables to get the combination of people's names and venues like this:

SELECT  p.NAME AS PersonName ,
        v.NAME AS VenueName
FROM    People p
        CROSS JOIN Venue v;

Next step would be to calculate the distance. Assuming you are using geography data types, google for distance between two pairs of latitudes and longitudes, for example here.

To convert this data to the cross tab format that you want to get it to, you will need to PIVOT. If you know the venues in advance, you can use the PIVOT operator in T-SQL. If not, you will need to use dynamic SQL, or do the pivoting in your presentation layer such as SQL Reporting Services. If you want to use dynamic SQL, look up this article.