For XML Path - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

For XML Path

Author

Topic

spareus
Yak Posting Veteran

52 Posts

Posted - 2014-01-07 : 10:07:11

Getting error
Incorrect syntax near the keyword 'FOR'.

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

Pl help.

Regards,
Spareus

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-07 : 13:50:46

What version of SQL server are you using? You syntax appears to be correct to me.

spareus
Yak Posting Veteran

52 Posts

Posted - 2014-01-08 : 05:34:22

SQL2000.
But you are right. It works only above 2005.
Thanks for pointing out.

Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 07:02:30

for sql 2000 you need to use UDF method


CREATE FUNCTION ConcatValues
(
@TeamID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetVar varchar(8000)

SELECT @RetVar=COALESCE(@RetVar,'') +  ', ' + FirstName 
FROM TeamInfo 
WHERE TeamID = @TeamID
ORDER BY TeamID,FirstName

SET @RetVar= substring(@RetVar,3,8000)
RETURN (@RetVar)
END

then call it like below

SELECT
   t1.TeamID,
   MemberList = dbo.ConcatValues(t1.TeamID)
GROUP BY TeamID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources