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