Row Constructor

SELECT [Values].[Prefix] + [Values1].[Suffix] AS [Name]
     , [Values].[Number]
 FROM (VALUES ('a', 1),                        
              ('de', 2))   AS [Values] ( [Prefix], [Number] ),
      (VALUES ('pple'),
              ('ttach'),
              ('manda')) AS [Values1] ( [Suffix] )