SQL result as a list

Question

I am trying to build sql that will return clients for a specific account as a list.

To better explain myself, I attached a diagram of what I'm trying to accomplish.

As you can see from the SQL result, I would like SQL to display clients as a list for each invoices.number

How can I execute SQL (for SQL SERVER)?

Answer 1

declare @Invoices table(ID int, Number varchar(10)) declare @Customers table(ID int, Name varchar(20)) declare @InvoiceCustomers table(InvoiceID int, CustomerID int) insert into @Invoices values (1, 'INV01') insert into @Customers values (1, 'NAME1'),(2, 'NAME2'),(3, 'NAME3') insert into @InvoiceCustomers values (1, 1),(1, 2),(1, 3) select I.Number as InvoicesNumber, stuff((select ', '+C.Name from @Customers as C inner join @InvoiceCustomers as IC on C.ID = IC.CustomerID where IC.InvoiceID = I.ID for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as CustomersName from @Invoices as I