Combine multiple rows into one single column (MS Access)

Question

I am trying to build a query from a table (Tbl_XYZ) that will give me the information for the primary key all in one row rather then creating dups for that primary key and showing up with multiple rows. Example below.( this is just a sample)

Account Number(PK)	System_Name	Table_Name
012345	DW	Customer
012345	DW	Sales
012345	DW	Address
012345	AWS	aws_Cust
012345	AWS	aws_product

I was thinking about creating 2 separate tables for System_Name and Table_Name but it would still give me values in different rows since the table names are different.

My expectation : ( Of course, I will have to create separate queries for different system names (DW and AWS)

Account Number	System_Name	Table_Name
012345	DW	Customer / Sales / Address
012345	AWS	aws_Cust / aws_product

Any help with this would be greatly appreciated.

I am using MS Access 2013

Thanks.

• Akina: stackoverflow.com/q/2852892/10138734

Answer 1

Access is not the most flexible DBMS, but I believe you have a function called STRING_AGG(), that you could use like:

select account_number, system_name, string_agg(isnull(table_name, ' '),'/ ') as table_name
from tbl_xyz group by account_number, system_name;