SQL Server String_agg [With 12 Useful Examples]

In this SQL Server tutorial, we will learn and understand how the SQL Server String_Agg function works in the table. We will discuss and learn some examples so that you can understand the concept much better:

• SQL Server String_Agg Function
• SQL Server String_Agg Example
• SQL Server String_Agg Distinct
• SQL Server String_Agg Within Group
• SQL Server String_Agg Multiple Columns
• SQL Server String_Agg Order by
• SQL Server String_Agg Within Group Order By
• SQL Server String_Agg Year
• SQL Server String_Agg Yesterday
• SQL Server String_Agg Within Group Not Working
• SQL Server String_Agg Join
• SQL Server String_Agg Order By Not Working

SQL Server String_Agg

In this section, we will learn and understand how to use the SQL Server String_Agg function on the table by a query. And which will be explained with the help of an illustrated example.

The SQL Server String_Agg function is used to concatenate rows of strings into one string by using the special separator. It doesn’t add the separator at the end of the string for the result set.

Here is the syntax of the String_Agg function by the following query:

SYNTAX:

SELECT STRING_AGG ( input_string, separator ) [ order_clause ]
WITHIN GROUP (ORDER BY expression [ DESC | ASC])
FROM TABLE_NAME;

The syntax explanation:

• The INPUT_STRING can be any type that can convert VARCHAR and NVARCHAR during concatenation.
• The SEPARATOR is a separator used in the result string. It can be a literal or string variable.
• The ORDER_CLAUSE specifies the sorted order of concatenated results by the WITHIN GROUP clause.

NOTE: When executing the concatenation, the STRING_AGG method ignores NULL and does not add the separator for NULL. Here is an illustrated example of the STRING_AGG function on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT STRING_AGG(STUDENT_FIRSTNAME,' ; ') FIRSTNAME_LIST
FROM MIT_UNIVERSITY
GROUP BY GENDER;

As we see in the above query, the SELECT statement is used to retrieve all records of the GENDER column from the MIT_UNIVESITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column of the MIT_UNIVERSITY table with the GROUP BY clause.

It means that based on the string of the GENDER column, it will group the STUDENT_FIRSTNAME column from the MIT_UNIVERSITY table with the semicolon separator in the result set.

We haven’t used the ALIAS clause to shorter the STRING_AGG function with the AS keyword and given the name as the FIRSTNAME_LIST column. If we don’t use the ALIAS clause on the table in the query then also query will execute and a temporary name will be given for that function.

We hope that you have understood how to use the SQL Server STRING_AGG function on the table by the query. For better understanding, we have used an example and explained it in depth.

Read: Introduction to SQL Server Trigger

SQL Server String_Agg Example

We will learn how to use the SQL Server String_Agg function with the GROUP WITHIN clause on the table by the query. And which is explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_FIRSTNAME DESC) AS STUDENT_FIRSTNAME_LIST
FROM MIT_UNIVERSITY
GROUP BY GENDER;

In this preceding query, we have used the SELECT statement to retrieve all records of the GENDER column from the MIT_UNIVERSITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column which is used with the WITHIN GROUP clause of the MIT_UNIVERSITY table.

This function implies that it will first sort the STUDENT_FIRSTNAME column in the group using the SEMICOLON separator, and then utilize the ORDER BY clause from the MIT_UNIVERSITY database to sort in decreasing order using the WITHIN GROUP clause.

To short the function_name, we have used the ALIAS clause with the AS keyword and given the name as STUDENT_FIRSTNAME_LIST column from the output column_name in the result set.

We hope that you have understood the subtopic “SQL Server String_Agg Example” by using the STRING_AGG function on the table by the query. For better knowledge, we have used an example and described it in depth.

Read: SQL Server First Day Of Month

SQL Server String_Agg Distinct

In this SQL Server section, we will learn and understand how to use the SQL Server DISTINCT and STRING_AGG functions on the table by the query. And which will be explained with the help of the syntax and an illustrated example.

In the SQL Server, sometimes we want to have a distinct value of a specified column from the table. For this, we want to have to see the syntax of the DISTINCT clause and STRING_AGG function on the table by the following query:

SYNTAX:

SELECT DISTINCT expression, 
( input_string, separator ) [ order_clause ]
WITHIN GROUP (ORDER BY expression [ DESC | ASC])
FROM YOUR_TABLE_NAME;

Here is the syntax explanation:

• We use the DISTINCT clause on the NULL value of the column then it will keep one NULL value and eliminates other NULL values from the table in the result set. In other words, the DISTINCT function treats all NULL values as the same value.
• The query usually returns only the DISTINCT value from the specified column. By another way of saying it, it removes the duplicate values in the column from the result set.

Here is an illustrated example of the SQL Server DISTINCT and STRING_AGG functions on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT DISTINCT GENDER,
STRING_AGG(STUDENT_FIRSTNAME,' ; ')  AS STUDENT_FIRSTNAME_LIST
FROM MIT_UNIVERSITY
GROUP BY GENDER;

In this preceding query, the SELECT statement is used with the DISTINCT function on the GENDER column of the MIT_UNIVERSITY table. The DISTINCT function will select unique records of the GENDER column from the MIT_UNIVERSITY table.

The STRING_AGG function is used on the STUDENT_FIRSTNAME column with the semicolon ( ; ) separator on the MIT_UNIVERSITY table which means that it will concatenate all the strings of the STUDENT_FIRSTNAME column with the semicolon separator based on the GENDER column.

At the end of the query, we have used the GROUP BY clause on the GENDER column which helps to concatenate all strings of the STUDENT_FIRSTNAME column.

We hope that you have understood the subtopic “SQL Server String_Agg Distinct” with the help of STRING_AGG and DISTINCT functions on the table by the query. For a better explanation, we have used an illustrated example and explained it in depth.

Read: Temp table in stored procedure in SQL Server

SQL Server String_Agg Within Group

In this section, we will learn how to use the SQL Server STRING_AGG function with the WITHIN GROUP clause on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_FIRSTNAME DESC) AS STUDENT_FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY
GROUP BY GENDER;

In this preceding query, we have used the SELECT statement to retrieve all records of the GENDER column from the MIT_UNIVERSITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column which is used with the WITHIN GROUP clause of the HARVARD_UNIVERSITY table.

This function implies that it will first sort the STUDENT_FIRSTNAME column in the group using the SEMICOLON separator, and then utilize the ORDER BY clause from the HARVARD_UNIVERSITY database to sort in decreasing order using the WITHIN GROUP clause.

To short the function_name, we have used the ALIAS clause with the AS keyword and given the name as STUDENT_FIRSTNAME_LIST column from the output column_name in the result set.

We hope that you have understood the subtopic “SQL Server String_Agg Within Group” on the table by the query. For a better explanation, we have used an example and explained it in deepness.

Read: SQL Server User Permissions

SQL Server String_Agg Multiple Columns

In this SQL Server section, we will learn and understand how to use the SQL Server STRING_AGG function on the multiple columns of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') FIRSTNAME_LIST,
STRING_AGG(STUDENT_LASTNAME, ' | ') LASTNAME_LIST
FROM MIT_UNIVERSITY
GROUP BY GENDER;

As we see in the above query, the SELECT statement is used to retrieve all records of the GENDER column from the MIT_UNIVESITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column of the MIT_UNIVERSITY table with the GROUP BY clause.

It means that based on the string of the GENDER column, it will group the STUDENT_FIRSTNAME column from the MIT_UNIVERSITY table with the semicolon separator in the result set.

We have again used the STRING_AGG function on the STUDENT_LASTNAME column which helps to concatenate the strings of the STUDENT_LASTNAME column and grouped them straight lash( | ) separator.

And then grouped them based on the GENDER column by using the GROUP BY clause in the MIT_UNIVERSITY table.

With the AS keyword and the name FIRSTNAME_LIST, we haven’t used the ALIAS clause to shorten the STRING_AGG function. If the ALIAS clause is not used on the table in the query still it will run and a temporary name for the function will be assigned.

We hope that you have understood the method of SQL Server STRING_AGG function on the multiple columns of the MIT_UNIVERSITY table by the query. For a better explanation, we have used an example and explained it in deepness.

Read: Drop stored procedure SQL Server

SQL Server String_Agg Order By

In this section, we will learn how to use the SQL Serve String_Agg function with the ORDER BY clause on the table by the query. And which will be explained with the help of an illustrated example.

In SQL Server, the ORDER BY statement is used to fetch records from the table by the query which is either in ascending or descending order according to one or more columns.

• By default, the ORDER BY clause is used to arrange the columns in ascending order.
• We can use the ASC keyword for arranging in ascending order and the DESC keyword to arrange in descending order.

Here is an illustrated example of the SQL Server String_Agg function used with the ORDER BY clause on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') FIRSTNAME_LIST
FROM MIT_UNIVERSITY
GROUP BY GENDER
ORDER BY GENDER DESC;

In this preceding query, the SELECT statement is used to retrieve all records of the GENDER column from the MIT_UNIVERSITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column with the semicolon ( ; ) separator in the MIT_UNIVERSITY table based on the GROUP BY clause.

It means that it will concatenate all records of the STUDENT_FIRSTNAME column with the semicolon separator and then group them based on the GENDER column.

In the end, we first grouped the GENDER column based on the GROUP BY clause and then arrange the column in descending order by using the ORDER BY expression DESC keyword in the MIT_UNIVERSITY table.

We hope that you have understood how to use the SQL Server String_Agg function with the ORDER BY clause on the table by the query. For a better explanation, we have used an example and explained it in deepness.

Read: Alter view in SQL Server

SQL Server String_Agg Within Group Order By

In this SQL Server section, we will learn and understand how to use the SQL Server String_Agg function used with the WITHIN GROUP and ORDER BY clauses on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_FIRSTNAME DESC) AS STUDENT_FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY
GROUP BY GENDER
ORDER BY GENDER DESC;

In this preceding query, we have used the SELECT statement to retrieve all records of the GENDER column from the MIT_UNIVERSITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column which is used with the WITHIN GROUP clause of the HARVARD_UNIVERSITY table.

This function implies that it will first sort the STUDENT_FIRSTNAME column in the group using the SEMICOLON separator, and then utilize the ORDER BY clause from the HARVARD_UNIVERSITY database to sort in decreasing order using the WITHIN GROUP clause.

To short the function_name, we have used the ALIAS clause with the AS keyword and given the name as STUDENT_FIRSTNAME_LIST column from the output column_name in the result set.

In the end, we have first grouped the GENDER column by using the GROUP BY clause and then arranged the order in the descending order by using the ORDER BY clause which will help for records in concatenation as per the result set.

We hope that you have understood the subtopic “SQL Server String_Agg Within Group Order By” by using the STRING_AGG function with the ORDER BY clause on the table by the query. For better understanding, we have used an example and explained it in depth.

Read: How to call a view in SQL Server

SQL Server String_Agg Year

In this section, we will learn how to use the SQL Server STRING_AGG and YEAR functions on the table by a query. And which will be explained with the help of an illustrated example.

The SQL Server YEAR function is used to extract the year portion value from the column_name or expression from the table by the query. Here is a demonstrated example of the SQL Server STRING_AGG and YEAR function on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(YEAR(STUDENT_ADMITDATE),' ; ') AS STUDENT_FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY
GROUP BY GENDER;

As we see in the above query, the SELECT statement will retrieve all records of the GENDER column of the HARVARD_UNIVERSITY table.

The STRING_AGG function is used with the YEAR function on the HARVARD_UNIVERSITY table. The YEAR function extracts the year portion value from the STUDENT_ADMITDATE column in the HARVARD_UNIVERSITY table.

In the STRING_AGG function, it will concatenate all records of the STUDENT_FIRSTNAME with the SEMICOLON ( ; ) separator and group them based on the GENDER column from the HARVARD_UNIVERSITY table.

We hope that you have understood the subtopic “SQL Server String_Agg Year” clearly by using the STRING_AGG and the YEAR functions on the table by the query. For better illustration, we have used a sample example and explained it in profundity.

Read: How to use union in view SQL Server

SQL Server String_Agg Yesterday

We will understand and learn how to use the SQL Server STRING_AGG and DATEADD functions on the table by the query. And which will be explained with the help of an illustrated example and a syntax.

In SQL Server, the DATEADD function is used to add a number for the specified part of the input value and it returns a modified value. Here is the syntax of the DATEADD function by the following query:

SYNTAX:

SELECT EXPRESSION, STRING_AGG(DATEADD(YOUR_DATE_PART, VALUE, INPUT_VALUE) , SEPARATOR)
FROM YOUR_TABLE_NAME;

Here is the syntax explanation:

• The YOUR_DATE_PART is a portion of the DATE that is passed to the DATEADD function, which adds the number to the date.
• VALUE is an integer value appended to the INPUT_VALUE’s YOUR_DATE_PART. If we use a decimal or float integer number, the DATEADD function will truncate the decimal fraction part. In this scenario, it will round the number.
• The input DATE is a direct data value or a phrase that resolves to a DATETIME, DATE, DATETIMEOFFSET, SMALLATETIME, TIME or DATETIME2 value.

Let’s see an illustrated example of the SQL Server STRING_AGG and DATEADD functions on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(DATEADD(DAY,-1,STUDENT_ADMITDATE),' ; ') AS STUDENT_ADMISSON_LIST
FROM HARVARD_UNIVERSITY
GROUP BY GENDER;

As we see in the above query, the SELECT statements will retrieve all records of the GENDER column from the HARVARD_UNIVERSITY table.

The DATEADD function is used inside the STRING_AGG function on the HARVARD_UNIVERSITY table by using the SELECT statement. The DATEADD function is used on the STUDENT_ADMITDATE column to subtract one day and return the DAY value in the HARVARD_UNIVERSITY table.

Then the STRING_AGG function will concatenate the DAY value which came from the DATEADD function by a semicolon ( ; ) separator. They have used the GROUP BY clause to group the STUDENT_ADMITDATE column based on the GENDER column records from the HARVARD_UNIVERSITY table.

To shorten the function_name, we used the ALIAS clause with the AS keyword and gave the name STUDENT_ADMISSION_LIST for the output column_name in the result set.

We hope that you have understood the subtopic “SQL Server String_Agg Yesterday” clearly by using the STRING_AGG and DATEADD functions properly on the table. For better understanding, we have used an example and explained it in depth.

Read: View SQL Server Error Logs

SQL Server String_Agg Within Group Not Working

We will learn and understand why the SQL Server STRING_AGG function is not working on the table by the query. And which will be explained with the help of an illustrated example.

ERROR EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_FIRSTNAME DESC) AS STUDENT_FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY;

In this preceding query, the SELECT statement will not be able to retrieve any records and even the STRING_AGG function will not be able to work because it requires the GROUP BY clause at the end of the HARVARD_UNIVERSITY table. Due to this, it throws an error in the query.

As the error says “Msg 8120, Level 16, State 1, Line 3 Column ‘HARVARD_UNIVERSITY.GENDER’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”.

ERROR REMOVED EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  GENDER,STRING_AGG(STUDENT_FIRSTNAME,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_FIRSTNAME DESC) AS STUDENT_FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY
GROUP BY GENDER;

In this preceding query, we have used the SELECT statement to retrieve all records of the GENDER column from the MIT_UNIVERSITY table. The STRING_AGG function is used on the STUDENT_FIRSTNAME column which is used with the WITHIN GROUP clause of the HARVARD_UNIVERSITY table.

This function implies that it will first sort the STUDENT_FIRSTNAME column in the group using the SEMICOLON separator, and then utilize the ORDER BY clause from the HARVARD_UNIVERSITY database to sort in decreasing order using the WITHIN GROUP clause.

To short the function_name, we have used the ALIAS clause with the AS keyword and given the name as STUDENT_FIRSTNAME_LIST column from the output column_name in the result set.

We hope that you have understood the sub-topic “SQL Server String_Agg Within Group Not Working” on the table by the query. For a better description, we have used an example and explained it in depth.

Read: Create a table from view in SQL Server

SQL Server String_Agg Join

In this SQL Server section, we will learn how to use the SQL Server STRING_AGG function with the JOIN clause on the tables by the query. And which will be explained with the help of an illustrated example.

In SQL Server,  JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause. The JOIN clause can be used when there are two or more two tables with common columns.

There are four types of JOIN in SQL Server:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

Here is an illustrated example of the SQL Server STRING_AGG function with the JOIN clause of the tables by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  HARVARD_UNIVERSITY.GENDER,STRING_AGG(HARVARD_UNIVERSITY.STUDENT_FIRSTNAME,' ; ') 
AS STUDENT_FIRSTNAME_LIST, MIT_UNIVERSITY.GENDER, 
STRING_AGG(MIT_UNIVERSITY.STUDENT_FIRSTNAME,' ; ') FIRSTNAME_LIST
FROM HARVARD_UNIVERSITY
INNER JOIN MIT_UNIVERSITY
ON 
HARVARD_UNIVERSITY.STUDENT_ID=MIT_UNIVERSITY.STUDENT_ID
GROUP BY HARVARD_UNIVERSITY.GENDER, 
MIT_UNIVERSITY.GENDER;

In the preceding query, the SELECT statement is used to retrieve all records of the GENDER column from both tables i.e; HARVARD_UNIVERSITY and MIT_UNIVERSITY. The STRING_AGG function is used on the STUDENT_FIRSTNAME columns of both tables i.e; HARVARD_UNIVERSITY and MIT_UNIVERSITY.

The function will concatenate all records of the STUDENT_FIRSTNAME columns from both tables with the semicolon ( ; ) separator based on the GROUP BY clause.

And on the bases of the INNER JOIN clause, the HARVARD_UNIVERSITY table and MIT_UNIVERSITY table will connect through a common column i.e; the STUDENT_ID column. In the end, we have grouped multiple columns of the same name from both tables i.e; the GROUP column which will help in the STRING_AGG functions.

To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name STUDENT_FIRSTNAME_LIST and FIRSTNAME_LIST for the output column_name in the result set by using the SELECT statement.

We hope that you have understood the subtopic “SQL Server String_Agg Join” by using the STRING_AGG function with the JOIN clause on the tables by the above query. For a better explanation, we have used an example and explained it in deepness.

Read: How to see view definition in SQL Server

SQL Server String_Agg Order By Not Working

In this section, we will learn why the SQL Server STRING_AGG used with the ORDER BY clause is not working on the table by the query. And which will be explained with the help of an illustrated example.

ERROR EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  STUDENT_FIRSTNAME,STRING_AGG(STUDENT_ADMITDATE,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_ADMITDATE) AS STUDENT_DATE_LIST
FROM HARVARD_UNIVERSITY
GROUP BY STUDENT_ID;

in this preceding query, the SELECT statement may not be able to retrieve any records of any column from the HARVARD_UNIVERSITY table. And the STRING_AGG function will not be able to execute it properly because we need to retrieve the same column in the SELECT statement which is STUDENT_ID.

It will become easier and can group its records for the STRING_AGG function. Here the error that says in the image below “Msg 8120, Level 16, State 1, Line 3 Column ‘HARVARD_UNIVERSITY.STUDENT_FIRSTNAME’ is invalid in the select list because it is not contained either aggregate function or the GROUP BY clause.

ERROR REMOVED EXAMPLE:

USE SQLSERVERGUIDES;

SELECT  STUDENT_ID,STRING_AGG(STUDENT_ADMITDATE,' ; ') 
WITHIN GROUP (ORDER BY STUDENT_ADMITDATE DESC) AS STUDENT_DATE_LIST
FROM HARVARD_UNIVERSITY
GROUP BY STUDENT_ID;

As we see in the above query, the SELECT statement will retrieve all records of the STUDENT_ID column from the HARVARD_UNIVERSITY table.

The STRING_AGG function is used with the WITHIN GROUP clause on the STUDENT_ADMITDATE column.

This function will concatenate all records of the STUDENT_ADMITDATE columns and arrange them in descending order by using the ORDER BY and the WITHIN GROUP clauses on the HARVARD_UNIVERSITY table.

In the end, we have grouped the STUDENT_ID column which will help for the concatenation of all records of the STUDENT_ADMITDATE columns in the STRING_AGG function.

We hope that you have understood how the SQL Server STRING_AGG function works in the table by the query. For a better explanation of the error, we have used two examples and explained it in the depth.

You may also like to read the following SQL Server tutorial.

• Instead of Trigger In SQL Server
• Comparison Operators in SQL Server
• How to delete a view in SQL Server
• SQL Server bulk insert from CSV file
• SQL Server function return table
• Trigger to insert data in another table in SQL Server

So, in this tutorial, we have learned how to use the SQL Server String_Agg function and we also covered the following set of topics:

• SQL Server String_Agg Function
• SQL Server String_Agg Example
• SQL Server String_Agg Distinct
• SQL Server String_Agg Within Group
• SQL Server String_Agg Multiple COlumns
• SQL Server String_Agg Order by
• SQL Server String_Agg Within Group Order By
• SQL Server String_Agg Year
• SQL Server String_Agg Yesterday
• SQL Server String_Agg Within Group Not Working
• SQL Server String_Agg Join
• SQL Server String_Agg Order By Not Working