1

I'm trying to perform string aggregation on an Address in T-SQL so that any of the Address's fields which are not NULL are used in a comma-separated string. I know how I would do this in C# but am lost with SQL.

Here's what I have so far (I need to convert the C# part into SQL):

SELECT STRING_AGG
(
    --Start of C# (I don't know how to convert this into SQL).
    new[]
    {
        Addresses.Line1,
        Addresses.Line2,
        Addresses.Line3,
        Addresses.City,
        Addresses.County,
        Addresses.State,
        Countries.Name,
        Addresses.Postcode
    }
    .Where(data => data != null)
    --End of C#
    ,
    ', '
) Address
FROM Addresses
JOIN Countries ON Countries.Id = Addresses.CountryId
Matt Arnold
  • 668
  • 2
  • 8
  • 21

1 Answers1

2

Personally, I'd bring them back as columns and worrying about formatting in the application code, however!

STRING_AGG is really intended for concatenating row data; you are working with columns, so you're probably better off doing something more like:

SELECT CONCAT(Addresses.Line1,
    N', ' + Addresses.Line2,
    N', ' + Addresses.Line3,
    -- ...
    N', ' + Addresses.Postcode)

CONCAT ignores null values, and N',' + {some column} will be null if {some column} is null, so this removes the intermediate separators for all omitted values. It does kinda presume that Line1 has a value - without that, there may be a leading comma. You could remove that if needed.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Well it's not perfect but it's good enough for the purpose I need it for (`Line1` is mandatory if entered through the UI). – Matt Arnold Mar 03 '20 at 12:03
  • I did wonder whether there was a way to pivot the columns into rows using the `sys.columns` or `INFORMATION_SCHEMA` tables - it's probably overkill for my example but I would be interested to see whether it's possible. – Matt Arnold Mar 03 '20 at 12:10