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.