Hi All,

Thanks a lot for your help,

I have done this using Cursors,

Here is the complete code,

DECLARE @tblRolesWithComma TABLE(Roles VARCHAR(8000))

insert into @tblRolesWithComma VALUES('Role1,Role2,')

insert into @tblRolesWithComma VALUES('Role1,Role3')

insert into @tblRolesWithComma VALUES('Role1,Role4')

insert into @tblRolesWithComma VALUES('Role2,Role5')

insert into @tblRolesWithComma VALUES('Role6,Role1')

insert into @tblRolesWithComma VALUES('Role7')

insert into @tblRolesWithComma VALUES('Role8')

SELECT * FROM @tblRolesWithComma

DECLARE @tblRolesInSingleColumn TABLE(RoleId VARCHAR(8000))

DECLARE cur_Roles CURSOR

READ_ONLY

FOR (SELECT * FROM @tblRolesWithComma)

DECLARE @RoleIds VARCHAR(8000)

OPEN cur_Roles

declare @pos int

declare @RoleId VARCHAR(500)

FETCH NEXT FROM cur_Roles INTO @RoleIds

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

IF RIGHT(RTRIM(@RoleIds),1) <> ','

SET @RoleIds = @RoleIds + ','

SET @pos = PATINDEX('%,%' , @RoleIds)

WHILE @pos <> 0

BEGIN

SET @RoleId = left(@RoleIds, @pos - 1)

INSERT INTO @tblRolesInSingleColumn VALUES(CAST(@RoleId as varchar(500)))

SET @RoleIds = STUFF(@RoleIds, 1, @pos, '')

SET @pos = PATINDEX('%,%' , @RoleIds)

END

END

FETCH NEXT FROM cur_Roles INTO @RoleIds

END

CLOSE cur_Roles

DEALLOCATE cur_Roles

--SELECT DISTINCT * FROM @tblRolesInSingleColumn

DECLARE @Result varchar(8000)

SET @Result = ''

SELECT @Result = @Result + CASE WHEN LEN(@Result)>0 THEN ', ' ELSE '' END + RoleId

FROM (SELECT DISTINCT RoleId FROM @tblRolesInSingleColumn)Roles

SELECT @Result RolesIds

SET NOCOUNT OFF

GO

Thank you very much dudes for you help

-----

Santosh