Skip to main content

TSQL: Create Comma Separated List from Multiple Rows with STRING_AGG() Function

In SQL Server, you can easily create a comma-separated list from multiple rows using the powerful STRING_AGG() function. This function simplifies the process of aggregating values into a single string, separated by a specified delimiter, and even allows you to control the order within the aggregation.

Sample Data to Create Comma Separated List from Multiple Rows​

You can use this DB Fiddle to follow along and practice using STRING_AGG() function to create comma-separated list from multiple rows

Let's say we have a "Items" table. We will use the built-in STRING_AGG() function to create comma-separated list in many different ways.

Table: Items

ItemIDItemNameItemCategoryItemDescription
1Item ACategory 1Description for A
2Item BCategory 1Description for B
3Item CCategory 2Description for C
4Item DCategory 2Description for D
5Item ECategory 1Description for E

Comma-Separated List of Single Column Values from Multiple Rows​

The SQL query aggregates data from the ItemName column of the Items table. It creates a single string by concatenating all ItemName values together, separated by commas. The result is a comma-separated list of ItemName values from the entire table. The alias ItemNameListAsc represents the ascending order of the list (natural order as it appears in the table).

Create a Comma-Separated List of Single Column Values from Multiple Rows
SELECT STRING_AGG(ItemName, ', ') AS ItemNameListAsc
FROM dbo.Items;
ItemNameListAsc
Item A, Item B, Item C, Item D, Item E

Comma-Separated List of Single Column Values Sorted Descendingly from Multiple Rows​

The SQL query aggregates data from the ItemName column of the Items table. It creates a single string by concatenating all ItemName values together, separated by commas. The WITHIN GROUP(ORDER BY ItemName DESC) clause sorts the ItemName values in descending alphabetical order within the aggregation. The result is a comma-separated list of ItemName values from the entire table, sorted in descending alphabetical order. The alias ItemNameListDesc represents the descending order of the list.

Create a Comma-Separated List of Single Column Values (Sorted Descendingly) from Multiple Rows
SELECT STRING_AGG(ItemName, ', ')WITHIN GROUP(ORDER BY ItemName DESC) AS ItemNameListDesc
FROM dbo.Items;
ItemNameListDesc
Item E, Item D, Item C, Item B, Item A

Comma-Separated List of Multiple Column Values from Multiple Rows​

The SQL query aggregates data from the ItemID, ItemName, and ItemDescription columns of the Items table. It creates a single string by concatenating these values together using the CONCAT_WS('|', ItemID, ItemName, ItemDescription) function. The CONCAT_WS function adds a pipe (|) separator between non-null values.

The WITHIN GROUP(ORDER BY ItemName) clause sorts the concatenated values based on the ItemName column in ascending alphabetical order within the aggregation.

The result is a comma-separated list of concatenated ItemID, ItemName, and ItemDescription values from the entire table, where each value set is separated by a comma and ordered by ItemName. The alias ConcatItemDetails represents this concatenated and sorted list.

Create a Comma-Separated List of Multiple Column Values from Multiple Rows
SELECT STRING_AGG(CONCAT_WS('|', ItemID, ItemName, ItemDescription), ', ')WITHIN GROUP(ORDER BY ItemName) AS ConcatItemDetails
FROM dbo.Items;
ConcatItemDetails
1|Item A|Description for A, 2|Item B|Description for B, 3|Item C|Description for C, 4|Item D|Description for D, 5|Item E|Description for E

Comma-Separated List of Single Column Values from Multiple Rows for Each Category​

The SQL query groups data from the Items table by ItemCategory. It creates a comma-separated list of ItemName values for each category. The result displays each ItemCategory along with its corresponding list of item names.

Create a Comma-Separated List of Single Column Values from Multiple Rows for Each Category
SELECT ItemCategory,
       STRING_AGG(ItemName, ', ') AS ItemList
FROM dbo.Items
GROUP BY ItemCategory;
ItemCategoryItemList
Category 1Item A, Item B, Item E
Category 2Item C, Item D

Comma-Separated List of Single Column Values Sorted Descendingly from Multiple Rows for Each Category​

The SQL query groups data from the Items table by ItemCategory. It then creates a comma-separated list of ItemName values for each category, sorting them in descending alphabetical order. The result displays each ItemCategory along with its sorted list of item names.

Create a Comma-Separated List of Single Column Values (Sorted Descendingly) from Multiple Rows for Each Category
SELECT ItemCategory,
       STRING_AGG(ItemName, ', ')WITHIN GROUP(ORDER BY ItemName DESC) AS ItemListSorted
FROM dbo.Items
GROUP BY ItemCategory;
ItemCategoryItemListSorted
Category 1Item E, Item B, Item A
Category 2Item D, Item C

Comma-Separated List of Multiple Column Values from Multiple Rows for Each Category​

The SQL query retrieves data from a table named Items. It groups the data by the ItemCategory column and then aggregates information about each group's items. The aggregated data includes ItemID, ItemName, and ItemDescription, which are combined with a pipe (|) separator using the CONCAT_WS function. The grouped data is further combined into a comma-separated list using the STRING_AGG function. This list is sorted alphabetically based on ItemName within each category. The final result displays each ItemCategory along with the formatted and sorted list of items.

Create a Comma-Separated List of Multiple Column Values from Multiple Rows for Each Category
SELECT ItemCategory,
       STRING_AGG(CONCAT_WS('|', ItemID, ItemName, ItemDescription), ', ')WITHIN GROUP(ORDER BY ItemName) AS ItemListSorted
FROM dbo.Items
GROUP BY ItemCategory;
ItemCategoryItemListSorted
Category 11|Item A|Description for A, 2|Item B|Description for B, 5|Item E|Description for E
Category 23|Item C|Description for C, 4|Item D|Description for D

STRING_AGG() Conclusion​

In conclusion, the STRING_AGG() function in SQL Server provides a powerful and efficient way to aggregate and format data into a single, comma-separated string. By concatenating values from multiple rows and allowing for sorting within the aggregation, it simplifies the process of generating concise and organized lists. Whether it's combining column values with a custom delimiter, grouping data by a specific criterion, or arranging the output in a desired order, the STRING_AGG() function proves to be a versatile tool for transforming raw data into meaningful and well-structured results. Its ability to streamline complex concatenation tasks enhances both the readability of query results and the efficiency of data retrieval, making it a valuable asset in SQL programming.