Menu
I have two tables "one to many":
Table1
ID Name
1 Abe
2 David
3 Orly
Table2
ID email
1 [email protected]
1 [email protected]
1 [email protected]
2 [email protected]
2 [email protected]
3 [email protected]
3 [email protected]
I need an output like this:
1 Abe [email protected], [email protected], [email protected]
2 David [email protected], [email protected]
3 Orly [email protected], [email protected]
I know this won't work, because the inner SELECT is not a single string:
SELECT
ID, Name,
(SELECT email FROM Table2 WHERE Table2.ID = Table1.ID) AS emails
FROM Table1
I tried to apply:
DECLARE @emails VARCHAR(999)
SELECT [ID],[Name],
(SELECT @emails = COALESCE(@emails + ', ', '') + [email]
FROM Table2) AS 'emails'
FROM Table1
but with no luck.
How should this be solved?
Thanks.
503
To merge two or more rows into one, here's what you need to do: Select the range of cells where you want to merge rows. Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.
You can use listagg() to convert rows into a comma separated string.
To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function.
One of the neatest ways to achieve this is to combine For XML Path and STUFF as follows:
SELECT
ID, Name,
Emails = STUFF((
SELECT ', ' + Email FROM Table2 WHERE Table2.ID = Table1.ID
FOR XML PATH ('')),1,2,'')
FROM Table1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
