How to Use STRING_AGG to Concatenate Strings in SQL Server?

In SQL Server, string concatenation can be a powerful tool for aggregating data and creating concise, informative results. One of the most effective functions for this purpose is STRING_AGG.

In this article, We will explore how to use STRING_AGG to concatenate strings in SQL Server, with the help of various examples and so on.

How to Concatenate Strings Using STRING_AGG?

When working with SQL Server, we may often need to concatenate strings from multiple rows into a single, comma–separated value. Traditionally, this task was complex, requiring complex queries and multiple joins. However, with the introduction of the STRING_AGG function in SQL Server 2017, this process has become much simpler and more efficient.

We use the STRING_AGG function along with the SELECT statement to check the info of what all different rows got grouped while using the aggregation functions like SUM, AVG, and COUNT with the GROUP BY clause of the SQL server.

Syntax:

SELECT AGGREGATE_FUNC(column_name) , STRING_AGG(column_name, -) 
GROUP BY column_name1, column_name2, column_name3

Explanation :

• AGGREGATE_FUNC: It is the aggregate function that we want to apply on the columns like SUM, AVG, COUNT, etc.
• STRING_AGG (column_name, separator)columns: This function concatenates the strings of the column with the given separator.
• GROUP BY: With this clause we can mention what column we want to group for applying the aggregate functions.

Let’s set up an environment for Concatenate Strings

We will create a table student_marks which contains the marks of the different subjects students in different sections in different subjects. The following query creates the table student_marks and inserts the rows into the table.

CREATE TABLE student_marks
(
student_id INT,
section VARCHAR(100),
subject_name VARCHAR(100),
marks_obtained INT,
);

INSERT INTO student_marks
VALUES
(1, 'A', 'MATHS', 96),
(2, 'A', 'MATHS', 75),
(3, 'A', 'MATHS', 91),
(4, 'A', 'MATHS', 86),
(5, 'A', 'MATHS', 100),
(1, 'B', 'MATHS', 96),
(2, 'B', 'MATHS', 90),
(3, 'B', 'MATHS', 91),
(1, 'C', 'MATHS', 86),
(2, 'C', 'MATHS', 76)

Output:

table

Explanation: Now that we have the table in place, lets go through the STRING_AGG function in SQL server for strings concatenation using the GROUP BY

Examples of STRING_AGG Function to Concatenate Strings

Example 1: Average Marks by Section with Student Roll Numbers

Here in the query we selected the section wise average as section_avg and concatenated student ids as students we have applied the AVG function on marks_obtained by grouping them on the basis of section.

SELECT AVG(marks_obtained) AS section_avg, STRING_AGG(student_id, ',') AS students
FROM student_marks
GROUP BY section

Output:

Output

Explantion: In the output we can see that the section average along with the student ids in that section concatenated by ‘,’ separator.

Example 2: Concatenated Student IDs and Marks with a Pattern

In the query along with the student Id we to concatenate the student marks as well we have used the CONCAT function to concatenate the student_id along with the marks in a particular fashion. Then used the aggregate function AVG by grouping on the basis of section.

SELECT AVG(marks_obtained) AS section_avg, STRING_AGG(CONCAT('{',student_id, ':',marks_obtained,'}'),',') AS students
FROM student_marks
GROUP BY section

Output :

Output

Explanation: In the output we can see that the student_id with the marks got concatenated while using the group by clause for finding the average of each section.

Example 3: Ordering the Concatenated String in DESC Order

We can order the concatenated string in the order which we want using WITHIN GROUP() clause. This example is similar to the first example where we order the roll numbers of the students concatenated in descending order.

SELECT AVG(marks_obtained) AS section_avg, STRING_AGG(student_id, ',')  
WITHIN GROUP(ORDER BY student_id DESC) AS students
FROM student_marks
GROUP BY section

Output :

Output

Explanation: Using the WITHIN GROUP clause we have the sorted the value that got group concatenated in the descending order, In the output we can see that the students column all the roll numbers are in descending order.

Example 4: Concatenated Student IDs and Marks with Descending Order by Marks

In this query we have used the WITHIN GROUP clause to order the concatenated strings of a particular patterns with multiple columns student_id, marks_obtained on the basis of marks_obtained in the descending order of marks. This query is similar to the second example

SELECT AVG(marks_obtained) AS section_avg, STRING_AGG(CONCAT('{',student_id, ':',marks_obtained,'}'),',')
WITHIN GROUP(ORDER BY marks_obtained DESC) AS students
FROM student_marks
GROUP BY section

Output :

Output

Explanation: We have using the WITHING GROUP class along with the STRING_AGG to concatenate the strings of the pattern also ordering them in the descending order of the marks_obtained. In the output we can see that the student roll numbers are ordered on the basis of the marks obtained.

Conclusion

In conclusion, the STRING_AGG function in SQL Server is a powerful tool for concatenating strings and aggregating data. By understanding the STRING_AGG, you can simplify your SQL queries, improve performance, and enhance the readability of your code. Whether you’re working with simple string concatenation or complex data aggregation tasks is an effective function.