Please see my details below in bold text

DECLARE @TABLE AS TABLE(

[RequestID] INT,

[ApprovalName] VARCHAR(35))

INSERT INTO @TABLE([RequestID],[ApprovalName])

--- the below sample data i want query , i wnat use for tables which having data

SELECT 15, 'Manager'

UNION ALL SELECT 15, 'Director'

UNION ALL SELECT 16, 'Manager'

UNION ALL SELECT 16, 'Director'

UNION ALL SELECT 16, 'Procurement Head'

-- the below one is table which is having data

select RequestID,ApproveLevelName from dbo.ts_Status

--Now to the query

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

AS (SELECT [RequestID],

CAST('' AS VARCHAR(8000)),

CAST('' AS VARCHAR(8000)),

0

FROM @TABLE

GROUP BY [RequestID]

UNION ALL

SELECT p.[RequestID],

CAST([Approval List] + CASE

WHEN length = 0 THEN ''

ELSE ', '

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

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

length + 1

FROM cte c

INNER JOIN @TABLE p

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

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

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