On the MS SQL forum I saw interesting question, which goes like this:

Hello. I am struggling with a query that I know can be done but I'm just not seeing it.

MeetState=state where the track meet was held
TeamName=name of the team (of course)
TeamState=home state of the team

MeetState TeamName TeamState
FL Houston TX
FL Tampa FL
FL Detroit MI
TX Dallas TX
TX Houston TX
TX Austin TX
NY Dallas TX
NY Tampa FL
NY Phoenix AZ
NY Albany NY

What I am needing is a list of the track meets that were attended by an out-of-state team, along with a comma-delimited list of those teams.

With the above data, the results would look like this:

FL Houston,Detroit
NY Dallas,Tampa,Phoenix
(the TX meet would not be listed because all of the teams were also from TX)

I know that for someone out there this is a simple task and I am looking forward to learning something new.

Thank you.

And, aswer is:

DECLARE	@Sample TABLE
	(
		MeetState CHAR(2) NOT NULL,
		TeamName VARCHAR(20) NOT NULL,
		TeamState CHAR(2) NOT NULL
	)

INSERT	@Sample
	(
		MeetState,
		TeamName,
		TeamState
	)
VALUES	('FL', 'Houston', 'TX'),
	('FL', 'Tampa',   'FL'),
	('FL', 'Detroit', 'MI'),
	('TX', 'Dallas',  'TX'),
	('TX', 'Houston', 'TX'),
	('TX', 'Austin',  'TX'),
	('NY', 'Dallas',  'TX'),
	('NY', 'Tampa',   'FL'),
	('NY', 'Phoenix', 'AZ'),
	('NY', 'Albany',  'NY')

SELECT		ms.MeetState,
		STUFF(team.Name, 1, 1, '') AS TeamNames
FROM		(
			SELECT		MeetState
			FROM		@Sample
			WHERE		MeetState <> TeamState
			GROUP BY	MeetState
		) AS ms
CROSS APPLY	(
			SELECT DISTINCT	TOP(2147483647)
					',' + s.TeamName
			FROM		@Sample AS s
			WHERE		s.MeetState = ms.MeetState
					AND s.TeamState <> ms.MeetState
			ORDER BY	',' + s.TeamName
			FOR XML		PATH('')
		) AS team(Name)
ORDER BY	ms.MeetState

This just note to my self, I didn't test, but I maybe need it in the future.

---

Actually I found one better way:

SELECT
   t1.TeamID,
   MemberList = substring((SELECT ( ', ' + FirstName )
                           FROM TeamInfo t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

From here.