Sql Server Management Studio have some options to customizing the display like adding the literals in the sql query to change the column name in output. It also have an option to concatenate strings in records, selected by sql query in sql server. In this article we will do these tasks with some examples in sql.
Consider the following example that displays the Department ID and Department Names from the Department table of the Adventure Works database. The report should contain column headings different from those given in the table, as specified in the following format.
You can write the query in the following ways:
Similarly, you might be required to make results more explanatory. In such case, you can add more text to the values displayed by the columns by using literals. Literals are string values enclosed in single quotes and added to the SELECT statement. The literal value is printed in a separate column as they are written in the SELECT list. Therefore, literals are used for display purpose.
The following SQL query retrieves the department-Id and their name from the Department table and change the column header as specified in the query.
SELECT DepartmentID 'Department Number', Name 'Department Name'
FROM Department
The SQL Server will display the output of the query, as shown in the following figure
The following SQL query concatenates the data of the Name and GroupName columns of the Department table into a single column. Text values, such as “department comes under” and “group”, are concatenated to increase the readability of the output:
SELECT Name + ' department comes under ' + groupName + ' group' AS Department
FROM Department
When you execute the query, it will format the output according to above query. The SQL Server will display the output of the query, as shown in the following figure.
Retrieving specific Records
Retrieve Selected Rows and Calculate Column values
Customizing the Display
Sometimes, you might be required to change the way the data is displayed on the output screen. For example, if the names of columns are not descriptive, you might need to change the column headings by creating user-defined headings.Consider the following example that displays the Department ID and Department Names from the Department table of the Adventure Works database. The report should contain column headings different from those given in the table, as specified in the following format.
Department Number Department Name
You can write the query in the following ways:
- SELECT ‘Department Number’ – DepartmentID, ‘Department Name’ FROM HumanResources.Department
- SELECT DepartmentID ‘Department Number’, Name ‘Department Name’ FROM HumanResources.Department
- SELECT DepartmentID AS ‘Department Number’, Name AS ‘Department Name’ FROM HumanResources.Department
Similarly, you might be required to make results more explanatory. In such case, you can add more text to the values displayed by the columns by using literals. Literals are string values enclosed in single quotes and added to the SELECT statement. The literal value is printed in a separate column as they are written in the SELECT list. Therefore, literals are used for display purpose.
The following SQL query retrieves the department-Id and their name from the Department table and change the column header as specified in the query.
SELECT DepartmentID 'Department Number', Name 'Department Name'
FROM Department
The SQL Server will display the output of the query, as shown in the following figure
 |
| Add caption |
Concatenating the Text Values in the Output
As a database developer, you will be required to address requirements from various users, who might want to view results in different ways. You might be required to display the values of multiple columns in a single column and also to improve readability by adding a description with the column value. In this case, you can use the Concatenation operator. The concatenation operator is used to concatenate string expressions. They are represented by the + sign.The following SQL query concatenates the data of the Name and GroupName columns of the Department table into a single column. Text values, such as “department comes under” and “group”, are concatenated to increase the readability of the output:
SELECT Name + ' department comes under ' + groupName + ' group' AS Department
FROM Department
When you execute the query, it will format the output according to above query. The SQL Server will display the output of the query, as shown in the following figure.
Retrieving specific Records
Retrieve Selected Rows and Calculate Column values