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