I have the following requirement and hope someone can provide a general guideline on how this can be done in SQL Server:

Usage

1. Users sign up to the website
2. Then I matc开发者_如何学编程h these users based on the their location. So two close users are matched together.
3. The number of matches is not clear, it could be 0 or 1000 user matches in one area

Requirement

1. For each user, put all the matches in one record. Since SQL Server doesn't have concept of arrays how do I do this? Objects maybe?
2. Later in the site I need to sort the matches for a specific user based on time, distance etc.

Your basic assumption is wrong!

SQL does have arrays. In fact all SQL has is arrays; because a table is an array.

Let me explain. An array is a sequence of values. A table is also a sequence of values (and an order, if you have a column which provides sequence information.)

For your example lets say you have a user table.

UserID, UserName

In this table each row represents a single user. You do not expect there to be two rows with the same userID

You can "associate" each user with an array of user by creating another table which is an array of users they are close to.

UserID, NearUserID, [Distance]

In this case this table would have multiple rows with the same UserID. This creates your array. If you want an ordering you can add a sequence or order column.

Here is what some SQL would look like to see each user and all their NearUsers:

SELECT *
FROM Users
JOIN NearUsers ON Users.UserID = NearUsers.NearUserID

Hope this helps.

Lets say you want to cut the records in half (see comment) with a table that matches users together in both directions

UserID1, UserID2

Then the SQL above can change to

SELECT Users.UserID, COALESCE(U1.UserID2,U2.UserID1) AS NearUser
FROM Users
JOIN UsersNear U1 ON Users.UserID = UserID1
JOIN UsersNear U2 ON Users.UserID = UserID2

This would be smaller (you are not storing an array for each user just the relationships) and more efficient than creating an array for each user.

If your requirement is real, then the only way to do it is to use a XML column containing all of the matches, or perhaps to have a comma-delimited list of all of the matches.

You may want to find out why the requirement wants all of the matches in a single row. How will the callers use all of the matches that are in the one row?

What is your definition for 'close' users? Suburb, zip code, city????

1)If it is one of the above you are over complicating the whole thing, because all you will do then is to select all users where [zip,city,suburb,...] = 'X', then you can do exaclty what @Paul Sasik suggest and insert them into a seperate table, or just create a view or stored proc that will retrieve them for you when you need them

2)You almost need another table (or a rule) to define what closeness means in time, distance etc, and have a way to look it up and compare records. Once you can do that, you can create a user defined function that you can use in your queries.

@John S, I dont agree that XML is the 'only' way.