AdventureWorks Database: Form one long string to display the last and first name

SQL Query - AdventureWorks: Exercise-192 with Solution

192. From the following table write a query in SQL to form one long string to display the last name and the first initial of the vice presidents. Sort the result set in ascending order on lastname.

Sample table: sales.salesorderheader

Click to view Full table

Sample Solution:

SELECT concat(LastName ,',' ,' ' , SUBSTRING(FirstName, 1, 1), '.')  AS Name, e.JobTitle  
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle LIKE 'Vice%'  
ORDER BY LastName ASC;

OR

SELECT LastName || ',' ||' ' || SUBSTRING(FirstName, 1, 1) || '.'  AS Name, e.JobTitle  
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle LIKE 'Vice%'  
ORDER BY LastName ASC;

Sample Output:

name        |jobtitle                     |
------------+-----------------------------+
Duffy, T.   |Vice President of Engineering|
Hamilton, J.|Vice President of Production |
Welcker, B. |Vice President of Sales      |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Concatenate character and date data types.
Next: Return with a product line of R and a days to manufacture less than 4.