SELECT DISTINCT 
                      dbo.Tournaments.EventName, dbo.Tournaments.EventDate, dbo.Tournaments.EventScheduled, dbo.Tournaments.EventPlayed, dbo.Tournaments.Location, 
                      dbo.Availability.Status, dbo.Players.FirstName, dbo.Players.LastName
FROM         dbo.Availability INNER JOIN
                      dbo.Players ON dbo.Availability.PlayerID = dbo.Players.PlayerID INNER JOIN
                      dbo.Tournaments ON dbo.Availability.EventID = dbo.Tournaments.EventID

/* Example of Availability Table */
DECLARE @Availability TABLE ( EventID INT, PlayerID INT, Status CHAR(5) )
INSERT @Availability ( EventID, PlayerID, Status )
SELECT 1, 1, 'True' UNION
SELECT 1, 2, 'False' UNION
SELECT 1, 3, 'True'
/* Example of Players Table */
DECLARE @Players TABLE ( PLayerID INT, FirstName VARCHAR(25), LastName VARCHAR(25), UserName VARCHAR(25) )
INSERT @Players ( PLayerID, FirstName, LastName, UserName )
SELECT 1, 'Kevin', 'Smithe', 'Ksmith' UNION
SELECT 2, 'John', 'Doe', 'jDoe' UNION
SELECT 3, 'Frank', 'White', 'fwhite'
/* Example of Event Table */
DECLARE @Event TABLE ( EventID INT, EventName VARCHAR(25), EventDate DATETIME, EventScheduled CHAR(5), EventPlayed CHAR(5), Location VARCHAR(25) )
INSERT @Event ( EventID, EventName, EventDate, EventScheduled, EventPlayed, Location )
SELECT 1, 'Tourney1', '2/2/2008', 'True', 'False', 'Someplace' UNION
SELECT 2, 'Tourney2', '2/4/2008', 'False', 'False', 'Noplace'

/* Query */
DECLARE @PlayerList VARCHAR(100)

SELECT  @PlayerList = COALESCE(@PlayerList + ', ' + p.FirstName + ' '
                               + p.LastName, p.FirstName + ' ' + p.LastName,
                               @PlayerList)
FROM    @Availability AS a
        INNER JOIN @Players AS p ON a.PlayerID = p.PlayerID
        INNER JOIN @Event AS t ON a.EventID = t.EventID
WHERE   t.EventScheduled = 'True'
        AND a.Status = 'True'

SELECT  t.EventName,
        t.EventDate,
        t.Location,
        @PlayerList AS PlayerList
FROM    @Availability AS a
        INNER JOIN @Players AS p ON a.PlayerID = p.PlayerID
        INNER JOIN @Event AS t ON a.EventID = t.EventID
WHERE   t.EventScheduled = 'True'
        AND a.Status = 'True'
GROUP BY t.EventName,
        t.EventDate,
        t.Location

DECLARE @PlayerList VARCHAR(100)
 

SELECT  @PlayerList = COALESCE(@PlayerList + ', ' + p.FirstName + ' '
                               + p.LastName, p.FirstName + ' ' + p.LastName,
                               @PlayerList)
FROM    Availability AS a
        INNER JOIN Players AS p ON a.PlayerID = p.PlayerID
        INNER JOIN Tournaments AS t ON a.EventID = t.EventID
WHERE   t.EventScheduled = 'True'
        AND a.Status = 'True'

SELECT  t.EventName,
        t.EventDate,
        t.Location,
        @PlayerList AS PlayerList
FROM    Availability AS a
        INNER JOIN Players AS p ON a.PlayerID = p.PlayerID
        INNER JOIN Tournaments AS t ON a.EventID = t.EventID
WHERE   t.EventScheduled = 'True'
        AND a.Status = 'True'
GROUP BY t.EventName,
        t.EventDate,
        t.Location

quote:

---

Originally posted by jdaman

The recursive nature of the variable will iterate over every row returned from the select. The coalesce function allows the variable to retain each value and thus creates the comma delimited list. ( I hope I am making sense. )

In short it doesnt matter how many player names are returned to the variable. The only governing issue is how large of a varchar value you declare it as.

---