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:
(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.
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