MySQL MariaDB

STRING_AGG Function in SQL Server

The STRING_AGG function is a powerful feature that is introduced in the Microsoft SQL Server 2017 that allows us to concatenate and organize the data in a simplified and efficient manner. It offers various capabilities such as sorting concatenated values, grouping the expressions based on specific columns, and removing duplicate values.

Let us discuss about sorting the concatenated results, grouping the expressions based on specific columns, and removing the duplicate values in this article. By understanding these methods, we hope that the readers will have an in-depth knowledge of the STRING_AGG function and its practical applications.

Creation of a Sample Table

Let’s first create a sample table called “Hospital” and then insert it with relevant data to understand the “STRING_AGG” function in the SQL Server. Our “Hospital” table contains columns such as “First Name”, “Last Name”, “Hometown”, and “Age”.

CREATE TABLE Hospital (

FirstName VARCHAR(50) NULL,

LastName VARCHAR(50) NULL,

Hometown VARCHAR(50) NULL,

Gender VARCHAR (10) NULL,

Age INT NULL

);

GO

INSERT INTO Hospital (FirstName, LastName, Hometown, Age, Gender)

VALUES

('Aarav', 'Sharma', 'Mumbai', 32, 'Male'),

('Aanya', 'Patel', 'Ahmedabad', 25, 'Female'),

('Arjun', 'Gupta', 'Delhi', 38, 'Male'),

('Diya', 'Paul', 'Kolkata', 29, 'Female'),

('Ishaan', 'Malhotra', 'Chandigarh', 33, 'Male'),

('Myra', 'Singh', 'Jaipur', 27, 'Female'),

('Rahul', 'Kumar', 'Lucknow', 41, 'Male'),

('Sara', 'Chopra', 'Bengaluru', 35, 'Female'),

('Rohan', 'Banerjee', 'Kolkata', 31, 'Male'),

('Ananya', 'Gupta', 'Delhi', 26, 'Female');

Output:

select * from Hospital;

FirstName LastName Hometown Gender Age

Aarav Sharma Mumbai Male 32

Aanya Patel Ahmedabad Female 25

Arjun Gupta Delhi Male 38

Diya Paul Kolkata Female 29

Ishaan Malhotra Chandigarh Male 33

Myra Singh Jaipur Female 27

Rahul Kumar Lucknow Male 41

Sara Chopra Bengaluru Female 35

Rohan Banerjee Kolkata Male 31

Ananya Gupta Delhi Female 26

Use of STRING_AGG in SQL Server

1. Sort the Result with the STRING_AGG Function in SQL

We can present the concatenated string values in a specific order by sorting the result of the STRING_AGG function. This feature becomes especially valuable when we want to display the data in an organized and meaningful manner. Using the “WITHIN GROUP” clause and specifying the desired column for sorting, we can ensure that the concatenated string is arranged according to our requirements. Suppose we want to sort the “FirstName” column in our sample table using the STRING_AGG function. We can perform this command:

SELECT STRING_AGG(FirstName, ', ') WITHIN GROUP (ORDER BY FirstName ASC) AS SortedNames

FROM Hospital;

Output:

SortedNames

Aanya, Aarav, Ananya, Arjun, Diya, Ishaan, Myra, Rahul, Rohan, Sara

Here in the given example, the T-SQL query selects the “FirstName” column from the “Hospital” table and uses the STRING_AGG function to concatenate the names. By adding the “WITHIN GROUP” clause and specifying the “ORDER BY” statement with “FirstName ASC”, the names are sorted alphabetically. The result is a clear and organized concatenated string of first names separated by commas. This sorting capability simplifies the tasks such as generating the appointment lists or patient directories. The STRING_AGG function eliminates the need for further sorting logic and improves the usability of the concatenated string.

2. Group the Concatenated Expression with STRING_AGG

The STRING_AGG function not only allows us to sort the concatenated expressions but also provides the capability to group and concatenate the expressions based on a specific column. This feature is very important when we deal with data that needs to be organized and must be presented in a more structured manner. We can perform this by combining the GROUP BY clause with the STRING_AGG function. We can easily create the concatenated strings for each group. Suppose we want to group the patients based on their gender and create a concatenated string of their hometowns within each gender group.

SELECT Gender, STRING_AGG(Hometown, ', ') WITHIN GROUP (ORDER BY Hometown ASC) AS ConcatenatedHometowns

FROM Hospital

GROUP BY Gender

ORDER BY Gender ASC;

Output:

Gender ConcatenatedHometowns

Female Ahmedabad, Bengaluru, Delhi, Jaipur, Kolkata

Male Chandigarh, Delhi, Kolkata, Lucknow, Mumbai

Here in the T-SQL query, the combination of the GROUP BY clause and the STRING_AGG function in SQL Server allows us to group and concatenate the expressions. It provides us with valuable insights and facilitates the data analysis. Here in the query, we select the gender column and use the STRING_AGG function to concatenate the hometowns of patients within each gender group. The GROUP BY clause ensures that the concatenation is performed for each unique gender value in the table. Then, after specifying the “ORDER BY Hometown ASC” clause within the WITHIN GROUP statement, we can order the concatenated hometowns in ascending order. The output of this T-SQL query is a set of rows, with each row that represents a distinct gender and its corresponding concatenated hometowns.

3. Remove the Duplicate Values

We can remove the duplicate values from the result that is obtained through the STRING_AGG. To address this requirement, we can employ a two-tier query approach. First, remove the duplicate rows and get the unique values. Then, we utilize the STRING_AGG function to concatenate the unique expressions.

Suppose we want to remove the duplicate hometowns from the result of the STRING_AGG using the “Hospital” table. We can use the STRING_AGG function to perform this.

SELECT STRING_AGG(Hometown, ', ') WITHIN GROUP (ORDER BY Hometown)

FROM (

SELECT DISTINCT Hometown

FROM Hospital

) AS TMP_TBL;

Output:

(No column name)

Ahmedabad, Bengaluru, Chandigarh, Delhi, Jaipur, Kolkata, Lucknow, Mumbai

In this query, we select the distinct hometown values from the “Hospital” table and use the STRING_AGG function to concatenate them. The DISTINCT keyword makes sure that the duplicate hometowns are removed and it results in a set of unique hometown values.

Conclusion

The SQL Server STRING_AGG function is a very powerful tool for concatenating and organising the data. It allows us to remove the duplicate values, group the expressions based on certain columns, and sort the concatenated values. We may improve the data readability and obtain useful data from the concatenated results using this function.

About the author

Somdeb Nath

Somdeb Nath is a highly accomplished programmer with expertise in C, Java, Python, SQL, HTML, CSS, Android, Linux and JavaScript. He has authored 100+ high-quality articles on programming languages. With strong problem-solving skills and a passion for technology, Somdeb is dedicated to continuous learning. Engage with him on LinkedIn: linkedin.com/in/somdeb-nath23