SQL Magic row to column concatenation

Imagine you have a very simple data model with two tables, Invoice and InvoiceTaxes.

When creating a report, i would like to see all invoices and the taxes applied to each, but not as a 1 to n relationship, but rather as a sting concatenation.

i.e.

Invoice1, “TPS”
Invoice2, “TPS, TVQ”
Invoice3, “TVH”

I went around and found a bunch of different ways on StackOverflow, everyone single one looking super duper complicated…

Then i found something called String_AGG, and it’s available on Azure SQL right now !

select invoiceid,
(select string_agg(taxname, ', ') from invoiceTaxes where invoiceTaxes.invoiceID = invoicesid) as Taxes
from invoices

Voilà !

 
Comments

No comments yet.