Concatenating output from a SELECT statement is a pretty basic thing to do in SQL. The main ways to perform this would be to use either the CONCAT() function, the || operator or the + operator. It really all depends on which version of SQL you are using. The following examples use T-SQL (MS SQL Server 2005) so it uses the + operator but other SQL versions have similar syntax.
If you wanted to join two fields together for a full name:
SELECT (lname + ', ' + fname) AS Name
FROM tblCustomers
To add some static text to a value:
SELECT (lname + ' - SS') AS Name
FROM tblPlayers
WHERE PlayerPosition = 6
Or to select some text and an integer together:
SELECT (lname + cast(playerNumber as varchar) AS Name
FORM tblPlayers