Data into fields

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.