-1

In SQL Server 2017, I have a many-to-many table:

=========================
id     nameID     phoneID
=========================
1        1           1
2        2           2
3        1           3

Let's say nameID 1 is FK for John in a NameTable. phoneID 1 is FK in PhoneTable for 01 234 567 and phoneID 3 is FK for 02 345 678.

When I join tables I get two rows.

======================
name        phone
======================
John       01 234 567
John       02 345 678

However, I need a string in one line. How do I print name and phones in one line like this:

John, 01 234 567, 02 345 678

Edit1: I found here similar question and solution for Oracle with LISTAGG. Edit2: And here is a solution for T-SQL with FOR XML PATH.

closed as off-topic by mustaccio, Erik Darling, Vérace, kevinsky, Max Vernon Apr 16 at 19:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Erik Darling, Vérace, kevinsky, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • A join may be? – mustaccio Apr 15 at 22:19
  • Of course join is needed. The problem is I get two rows and I need all in one line. – Hrvoje T Apr 15 at 22:39
  • You forgot the link to the T-SQL FOR XML PATH solution. And, if that works, why not use it? – Vérace Apr 15 at 23:05
  • Well, I just found it. That is why it is called 'edit question'. I will use it when I test it and if it's an answer I'll post it. – Hrvoje T Apr 15 at 23:08
  • How are you going to join 1 to 'John' or '02 345 678'? – Vérace Apr 15 at 23:12
2

It depends on your SQL Server version. In SQL Server 2016 or higher, you can use STRING_AGG:

DECLARE @Names TABLE (ID INT IDENTITY, FirstName VARCHAR(25))
DECLARE @Phones TABLE (ID INT IDENTITY, Phone VARCHAR(25))
DECLARE @ManyToMany TABLE (ID INT IDENTITY, NameID INT, PhoneID INT)

INSERT INTO @Names 
VALUES ('John'), ('Mike')

INSERT INTO @Phones 
VALUES ('12 345 678'), ('00 987 654'), ('11 254 009')

INSERT INTO @ManyToMany (NameID, PhoneID)
VALUES (1, 1), -- John link #1
    (1, 3), --John link #2
    (2, 2) -- Mike link #1

SELECT n.FirstName AS [Name],
    STRING_AGG(p.[Phone], ',') AS [Phone]
FROM @ManyToMany m
INNER JOIN @Names n ON n.ID = m.NameID
LEFT JOIN @Phones p ON p.ID = m.PhoneID
GROUP BY n.FirstName

Results:

Name    Phone
-----------------------------
John    12 345 678,11 254 009
Mike    00 987 654

Not the answer you're looking for? Browse other questions tagged or ask your own question.