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”.
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:
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:
FROM Hospital;
Output:
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.
FROM Hospital
GROUP BY Gender
ORDER BY Gender ASC;
Output:
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.
FROM (
SELECT DISTINCT Hometown
FROM Hospital
) AS TMP_TBL;
Output:
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.
