Help With sp and Comma Delimited String

• johna1

  Ten Centuries

  Points: 1184

  More actions

  October 3, 2003 at 5:24 pm

  #81173

  Roles

  Admin

  User

  DeleteAll

  ReadAll

  Report

  The above "Roles" table contains a list of values. I have a stored procedure that must return a table with one field called retVal that is equal to a comma delimited list of all the values found in the above "Roles" table. Basically, I need it to return a table looking like this

  retVal='Admin,User,DeleteAll,ReadAll,Report'

  Is there an easy way to do this with SQL? I can use CURSORS and do row by row processing, but it seems like there should be an easier way. Does anyone know how to do this easily without CURSORS?

• winash

  SSChampion

  Points: 11446

  More actions

  October 5, 2003 at 8:56 pm

  #477116

  This is one way to go about it....

  Declare @RetVal Varchar(1000)

  SET @RetVal = ''

  SELECT @RetVal = @RetVal+','+(Select Min(RoleName) from Roles where Roles.RoleName >= Outer1.RoleName) from Roles Outer1

  SELECT Substring(@RetVal,2,Len(@RetVal))

  -- eliminate first ','