Menu

How to Use STRING_AGG() for Concatenating Rows into a Single Column in SQL Server

When working with SQL Server, you may encounter scenarios where you need to combine multiple row values into a single column. Prior to SQL Server 2017, this required using STUFF() with FOR XML PATH(), but now, the STRING_AGG() function provides a simpler approach.

What is STRING_AGG()?

The STRING_AGG() function concatenates values from multiple rows into a single string with a specified separator.

Basic Syntax:

SELECT STRING_AGG(column_name, ', ') AS concatenated_values
FROM table_name;
  • column_name: The column whose values you want to concatenate.
  • ', ': The separator used between values.

Example Usage

Consider a Customers table:

id name
1 Alice
2 Bob
3 Charlie

Using STRING_AGG(), we can concatenate the names:

SELECT STRING_AGG(name, ', ') AS customer_names
FROM Customers;

Result:

Alice, Bob, Charlie

Using STRING_AGG() with GROUP BY

You can also use STRING_AGG() within GROUP BY to aggregate data by a specific column. Consider an Orders table:

customer_id product
1 Laptop
1 Mouse
2 Keyboard
2 Monitor

To get a list of products purchased by each customer:

SELECT customer_id, STRING_AGG(product, ', ') AS purchased_products
FROM Orders
GROUP BY customer_id;

Result:

customer_id | purchased_products
------------|-------------------
1           | Laptop, Mouse
2           | Keyboard, Monitor

Sorting Values in STRING_AGG()

By default, STRING_AGG() does not guarantee an order. To enforce ordering, use WITHIN GROUP (ORDER BY column_name). Example:

SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS sorted_names
FROM Customers;

Key Benefits of STRING_AGG():

  • Eliminates complex workarounds like STUFF() with FOR XML PATH().
  • More readable and concise syntax.
  • Works efficiently with GROUP BY for aggregating related data.

STRING_AGG() is a powerful function that simplifies string concatenation in SQL Server, making queries cleaner and more efficient. Happy querying!

1
150

Related

When working with URLs in C#, encoding is essential to ensure that special characters (like spaces, ?, &, and =) don’t break the URL structure. The recommended way to encode a string for a URL is by using Uri.EscapeDataString(), which converts unsafe characters into their percent-encoded equivalents.

string rawText = "hello world!";
string encodedText = Uri.EscapeDataString(rawText);

Console.WriteLine(encodedText); // Output: hello%20world%21

This method encodes spaces as %20, making it ideal for query parameters.

For ASP.NET applications, you can also use HttpUtility.UrlEncode() (from System.Web), which encodes spaces as +:

using System.Web;

string encodedText = HttpUtility.UrlEncode("hello world!");
Console.WriteLine(encodedText); // Output: hello+world%21

For .NET Core and later, Uri.EscapeDataString() is the preferred choice.

28
1388

Removing duplicates from a list in C# is a common task, especially when working with large datasets. C# provides multiple ways to achieve this efficiently, leveraging built-in collections and LINQ.

Using HashSet (Fastest for Unique Elements)

A HashSet<T> automatically removes duplicates since it only stores unique values. This is one of the fastest methods:

List<int> numbers = new List<int> { 1, 2, 2, 3, 4, 4, 5 };
numbers = new HashSet<int>(numbers).ToList();
Console.WriteLine(string.Join(", ", numbers)); // Output: 1, 2, 3, 4, 5

Using LINQ Distinct (Concise and Readable)

LINQ’s Distinct() method provides an elegant way to remove duplicates:

List<int> numbers = new List<int> { 1, 2, 2, 3, 4, 4, 5 };
numbers = numbers.Distinct().ToList();
Console.WriteLine(string.Join(", ", numbers)); // Output: 1, 2, 3, 4, 5

Removing Duplicates by Custom Property (For Complex Objects)

When working with objects, DistinctBy() from .NET 6+ simplifies duplicate removal based on a property:

using System.Linq;
using System.Collections.Generic;

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

List<Person> people = new List<Person>
{
    new Person { Name = "Alice", Age = 30 },
    new Person { Name = "Bob", Age = 25 },
    new Person { Name = "Alice", Age = 30 }
};

people = people.DistinctBy(p => p.Name).ToList();
Console.WriteLine(string.Join(", ", people.Select(p => p.Name))); // Output: Alice, Bob

For earlier .NET versions, use GroupBy():

people = people.GroupBy(p => p.Name).Select(g => g.First()).ToList();

Performance Considerations

  • HashSet<T> is the fastest but only works for simple types.
  • Distinct() is easy to use but slower than HashSet<T> for large lists.
  • DistinctBy() (or GroupBy()) is useful for complex objects but may have performance trade-offs.

Conclusion

Choosing the best approach depends on the data type and use case. HashSet<T> is ideal for primitive types, Distinct() is simple and readable, and DistinctBy() (or GroupBy()) is effective for objects.

1
457

When working with SQL Server, you may often need to count the number of unique values in a specific column. This is useful for analyzing data, detecting duplicates, and understanding dataset distributions.

Using COUNT(DISTINCT column_name)

To count the number of unique values in a column, SQL Server provides the COUNT(DISTINCT column_name) function. Here’s a simple example:

SELECT COUNT(DISTINCT column_name) AS distinct_count
FROM table_name;

This query will return the number of unique values in column_name.

Counting Distinct Values Across Multiple Columns

If you need to count distinct combinations of multiple columns, you can use a subquery:

SELECT COUNT(*) AS distinct_count
FROM (SELECT DISTINCT column1, column2 FROM table_name) AS subquery;

This approach ensures that only unique pairs of column1 and column2 are counted.

Why Use COUNT DISTINCT?

  • Helps in identifying unique entries in a dataset.
  • Useful for reporting and analytics.
  • Efficient way to check for duplicates.

By leveraging COUNT(DISTINCT column_name), you can efficiently analyze your database and extract meaningful insights. Happy querying!

1
122