Merge values into single column and one row using FOR XML PATH

  ;WITH CTE_MERGE_VALUES

  AS 

  (

  SELECT [TYPE], 

[NAME]

  FROM MASTER..SPT_VALUES WHERE TYPE <> 'P'

  )

    SELECT [TYPE], 

[NAME],

[NAME2] = (SELECT STUFF((SELECT DISTINCT ' | ' +  CAST([NAME] AS NVARCHAR(MAX)) FROM CTE_MERGE_VALUES B WHERE [TYPE]=A.[TYPE] ORDER BY 1 ASC FOR XML PATH('')), 1, 3, ''))

FROM CTE_MERGE_VALUES A