thanks vinu

Now i am trying to join this query with a table to get the desire results

CREATE TABLE #tProduct

(

tProductID bigint PRIMARY KEY,

tProductCode nvarchar(128),

tProductName nvarchar(256),

tManufacturerName nvarchar(256),

tProductDescription nvarchar(4000)

)

EXECUTE ('SELECT *

FROM #tProduct AS P

JOIN(SELECT ProductID, +STUFF((Select DISTINCT '',MAX(Case When ParentFeatureName = '' + CHAR(39) + ParentFeatureName + CHAR(39) + '' Then ChilFeatureName Else '' + CHAR(39) + CHAR(39) + '' End) As '' + ParentFeatureName From #Test For XML PATH('')),1,1,'''')

+ FROM #Test Group By ProductID'' ) AS A ON P.tProductID = A.ProductID')

I am getting this error

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'AS'.