when execute stuff i get error on order by

Question

I work on sql server 2019 i have issue errors when execute statment below
stuff
but i don't know how to solve issue

select fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tr.codetypeid,tt.codetype,tt.PlName,tr.code  
,count(fd.zfeaturekey) as CountFeatures  
,stuff(( SELECT  '$' + CAST( CP.FeatureName  AS VARCHAR(500)) AS [text()]  
                    FROM(SELECT distinct C.partid, C.FeatureName,C.ZfeatureKey from extractreports.dbo.collectallfeatures  C with(nolock)   
					where C.partid=fd.partid and C.CodeTypeID=fd.CodeTypeID and c.Code=fd.code and c.zplid=fd.zplid  
					ORDER BY C.ZfeatureKey)CP  
					  
					--ORDER BY CP.ZfeatureKey  
		  
                   FOR XML PATH('')), 1, 1, NULL) as FeatureName  
				   ,stuff(( SELECT  '$' + CAST( COALESCE(CP2.FeatureValue, 'NULL') AS VARCHAR(500)) AS [text()]  
                    FROM(SELECT distinct C2.partId,C2.zvalue as FeatureValue,C2.ZfeatureKey FROM extractreports.dbo.collectallfeatures C2 with(nolock)  
					where C2.partid=fd.partid and C2.CodeTypeID=fd.CodeTypeID and c2.Code=fd.code and c2.zplid=fd.zplid)CP2  
					where CP2.PartId=fd.partid  
					ORDER BY CP2.ZfeatureKey  
                    FOR XML PATH('')), 1, 1, NULL) as FeatureValue   
into extractreports.dbo.collectallfeatureswithfeaturename  
from extractreports.dbo.collectallfeatures fd   
inner join parts.tradecodes tr with(nolock) on tr.partid=fd.partid and tr.codetypeid=fd.codetypeid and fd.code=tr.code and tr.partlevel=0  
inner join #TempTradeCode tt on tt.partid=tr.partid and tt.codetypeid=tr.codetypeid  and tt.CurrentPL=tr.zplid   
  
group by fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tt.codetype,tt.PlName,tr.code,tr.codetypeid  

error i get and i need to solve it

Msg 1033, Level 15, State 1, Line 6  
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.  
Msg 156, Level 15, State 1, Line 15  
Incorrect syntax near the keyword 'ORDER'.  
Msg 319, Level 15, State 1, Line 19  
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.  

so how to solve error above please

Answer 1

Since I don't have your tables, here is just the stub, you may want to play to adjust:

select fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tr.codetypeid,tt.codetype,tt.PlName,tr.code  
 ,count(fd.zfeaturekey) as CountFeatures  
 , STRING_AGG(FeatureName, '$') WITHIN GROUP (ORDER BY FeatureName ) AS FeatureName,  
 STRING_AGG(COALESCE(zValue, 'NULL'), '$') WITHIN GROUP (ORDER BY zFeatureKey) AS FeatureValue  
   
 into extractreports.dbo.collectallfeatureswithfeaturename  
 from extractreports.dbo.collectallfeatures fd   
 inner join parts.tradecodes tr with(nolock) on tr.partid=fd.partid and tr.codetypeid=fd.codetypeid and fd.code=tr.code and tr.partlevel=0  
 inner join #TempTradeCode tt on tt.partid=tr.partid and tt.codetypeid=tr.codetypeid  and tt.CurrentPL=tr.zplid   
      
 group by fd.partid,tt.PLID,tt.PartNumber,tt.CompanyName,tt.codetype,tt.PlName,tr.code,tr.codetypeid  

Answer 2

FROM(SELECT distinct C.partid, C.FeatureName,C.ZfeatureKey from extractreports.dbo.collectallfeatures C with(nolock)
where C.partid=fd.partid and C.CodeTypeID=fd.CodeTypeID and c.Code=fd.code and c.zplid=fd.zplid
ORDER BY C.ZfeatureKey)CP
The ORDER BY clause is invalid in views, inline functions,

You got a clear error message, the ORDER BY above in the inline view ist not allowed.

so how to solve error above please

Easy, remove that ORDER BY.