Numbering the Concatenation - 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

General SQL Server Forums

New to SQL Server Programming

Numbering the Concatenation

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-06 : 08:54:09

Hi ALL
I have the below query which concatenates the comments feild in a table and separates it by a coma.Is it possible to add sequential numbers for each comments

SELECT DISTINCT ReqNo,
STUFF(( SELECT ',' + COMMENTS
FROM Concatenating_comments WHERE ReqNo= T.ReqNo
FOR XML PATH('') 
),1,1,'') AS AGGREGATEDCOMMENTS
FROM Concatenating_comments T

Thanks
Jim

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-06 : 09:20:20

without a test but it should work:

SELECT DISTINCT 
ReqNo,
STUFF(( SELECT ',' + convert(varchar(10),rnum) + '. ' + COMMENTS
        FROM (select row_number() over (order by ReqNo asc) as rnum,
              comments,
              ReqNo 
              from Concatenating_comments) as Concatenating_comments 
        WHERE ReqNo= T.ReqNo
        FOR XML PATH('') 
     ),1,1,'') AS AGGREGATEDCOMMENTS
FROM Concatenating_comments T

No, you're never too old to Yak'n'Roll if you're too young to die.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-06 : 09:36:26

Thanks.It worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources