Thanks!

When i am modifying your query like below. I am getting zero rows.

Please can you check my query.

DECLARE @ts_vSignOffStatus AS TABLE(

[RequestID] INT,

[ApproveLevelName] VARCHAR(35))

--Now to the query

;WITH cte ( [RequestID], [Approval List], [ApproveLevelName], length )

AS (SELECT [RequestID],

CAST('' AS VARCHAR(8000)),

CAST('' AS VARCHAR(8000)),

0

FROM @ts_vSignOffStatus

GROUP BY [RequestID]

UNION ALL

SELECT p.[RequestID],

CAST([Approval List] + CASE

WHEN length = 0 THEN ''

ELSE ', '

END + p.[ApproveLevelName] AS VARCHAR(8000)),

CAST(p.[ApproveLevelName] AS VARCHAR(8000)),

length + 1

FROM cte c

INNER JOIN @ts_vSignOffStatus p

ON c.[RequestID] = p.[RequestID]

WHERE p.[ApproveLevelName] > c.[ApproveLevelName])

SELECT [RequestID],

[Approval List]

FROM (SELECT [RequestID],

[Approval List],

Rank() OVER ( PARTITION BY [RequestID] ORDER BY length DESC )

FROM cte) d ( [RequestID], [Approval List], rank )

WHERE rank = 1