0

I have following quary example.

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

Its giving error "Incorrect syntax near the keyword 'FOR'."
Pl help.

2
Contributors
6
Replies
19
Views
3 Years
Discussion Span
Last Post by Madiya122
0

Have you tried FOR XML AUTO ? Wondering why you don't specify a path?

If you just try:

SELECT FirstName
   FROM TeamInfo
   ORDER BY 
      TeamID,
      FirstName
   FOR XML AUTO

Do you get results?

Edited by pritaeas

0

Yes it works but with incorrect results as below.

    <TeamInfo FirstName="Bob"></TeamInfo><TeamInfo FirstName="Jim"></TeamInfo><TeamInfo FirstName="Mary"></TeamInfo><TeamInfo FirstName="Ellen"></TeamInfo><TeamInfo FirstName="Ralph"></TeamInfo><TeamInfo FirstName="Sue"></TeamInfo><TeamInfo FirstName="Bill"></TeamInfo><TeamInfo FirstName="Linda"></TeamInfo>

Expected results is 
TeamID     MemberList
1   Bob, Jim, Mary
2   Ellen, Ralph, Sue
3   Bill, Linda
1

Am sure you read from the article where you found this example that it is designed for SQL 2005 and up. Earlier versions don't support the FOR XML.

Votes + Comments
Thanks
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.