0

I have a table address and another addressline. One address can have multiple address lines.

I want a select statement that returns one row per address, and if there are multiple address lines, I still only want one row but with all the address lines comma-separated in one column.

I have only managed to do it like this but it returns the values as XML and I just want it as string

SELECT
    STUFF((SELECT ', ', adrline.AddressLine
           FROM AddressLine AS adrline
           WHERE adr.Id = adrline.AddressId
           FOR XML PATH('')), 1, 2, '') AS AddressLine,
FROM 
    Address AS adr

Result:

<AddressLine>My Street 1</AddressLine>, <AddressLine>My Street 2</AddressLine>

Desired result:

My Street 1, My Street 2

How can I do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
xeraphim
  • 4,375
  • 9
  • 54
  • 102
  • 1
    Side note `STUFF` isn't the function doing the work here; all `STUFF` is doing is removing the first 2 leading characters from the string. `FOR XML PATH` is doing the "aggregation". – Thom A Feb 24 '23 at 10:58
  • 1
    `', ' + adrline.AddressLine` not `', ', adrline.AddressLine`. – Thom A Feb 24 '23 at 10:59
  • @Larnu: that worked, thank you! If you add it as an answer, I'll gladly accept it :-) – xeraphim Feb 24 '23 at 11:02
  • In my opinion, it is more of a typographical error. Or, if not, a duplicate of something like [String\_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017) – Thom A Feb 24 '23 at 11:04
  • this code will mangle all "special" xml characters if they occur in the addressline. You might wanna take a look at FOR XML TYPE – siggemannen Feb 24 '23 at 12:40
  • which version of sql server are you using – sayah imad Feb 24 '23 at 12:50
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Feb 24 '23 at 15:12

1 Answers1

0

Recent version of SQL SERVER (2017 .... ) , offer a new feature to your need as you can see below, you can use the same things :

SELECT STRING_AGG([AddressLine], ', ') AS output
FROM Address;
sayah imad
  • 1,507
  • 3
  • 16
  • 24
  • Based on the OP's query you'll need to join the `Address` and `AddressLine` tables. And aggregation usually benefits from having a `group by` clause. – HABO Feb 24 '23 at 14:36
  • @HABO hi I'm not able to provide a complete solution because i haven't all the context, it's just suggestion to solve the problem – sayah imad Feb 25 '23 at 12:24