In SQLAlchemy how to merge multiple rows into one by converting unique column values into comma separated string?

user_17440421 0
Viewed 694

Sample table

Invoice Number Invoice Date Item Name
1001 12 Jul 21 Foo
1002 10 Jun 21 Baz
1001 12 Jul 21 Bar
1002 10 Jun 21 Spam
1001 12 Jul 21 Eggs

How can I write a sqlalchemy ORM query to fetch these data in the below format:

Invoice Number Invoice Date Items
1001 12 Jul 21 Foo,Bar, Eggs
1002 10 Jun 21 Baz, Spam

I am connecting to a sql server database in the backend.

sqlalchemy sql-server python
1 Answers

I haven't tested it with SQL-Server but I reckon that you could use the string_agg function.

The simpler version, if you can live with duplicate values in your comma separated list:

from sqlalchemy import func
from sqlalchemy.sql.elements import literal_column

result = session.query(
  Table.invoice_number,
  Table.invoice_date,
  func.string_agg(Table.item_name, literal_column("','"))
).group_by(
  Table.invoice_number,
  Table.invoice_date
).all()

And, if you need to avoid duplicate values:

sub_query = session.query(Table).distinct().subquery()

result = session.query(
  sub_query.c.invoice_number,
  sub_query.c.invoice_date,
  func.string_agg(sub_query.c.item_name, literal_column("','"))
).\
select_from(sub_query).\
group_by(
  sub_query.c.invoice_number,
  sub_query.c.invoice_date
).all()
user_178308150
Related