You need to specify the desired order within the sub-query.
Try something like this:
SELECT STUFF(
(SELECT ',' + CAST(FId As varchar(20))
FROM tbl_table
WHERE [Type] IN ('Address', 'Street', 'Number', 'Name')
ORDER BY CASE [Type]
WHEN 'Address' THEN 0
WHEN 'Street' THEN 1
WHEN 'Number' THEN 2
ELSE 3
END
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
), 1, 1, '') AS FID
The STUFF
function[
^] avoids having to provide an end point for the substring operation.
And adding
TYPE
and
.value('.', 'varchar(max)')
ensures there won't be any XML-encoded text in the output, as described in
this Simple Talk article[
^].