Member Avatar for Madiya122

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
  • 1K Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by Madiya122
Member Avatar for pritaeas

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
Member Avatar for Madiya122

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
Member Avatar for pritaeas

What version of MSSQL are you using?

Member Avatar for pritaeas

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.

commented: Thanks +0
Member Avatar for Madiya122

Well, yes. But I missed.
Thanks for your help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.