title: Using string_agg() function in sqlalchemcy with SQL Server 2017
body: <p>Task at hand is to aggregate the values into comma-separated string using SqlAlchemy with SQL Server 2017:</p>
  <p><a href="https://i.stack.imgur.com/UKQ9v.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UKQ9v.png" alt="enter image description here" /></a></p>
  <p>So I use <code>string_agg()</code> to do so:</p>
  <pre><code>query = session.query(Table.key, func.string_agg(Table.value, ',')).group_by(Table.key).all()
  </code></pre>
  <p>But the query always returns an error:</p>
  <blockquote>
  <p>Argument data type nvarchar is invalid for argument 2 of string_agg function</p>
  </blockquote>
  <pre><code>sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Argument data type nvarchar is invalid for argument 2 of string_agg function. (8116) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')
  </code></pre>
  <p>I tried a number of methods to avoid this error (e.g. cast the constant parameter &quot;,&quot; to string, or using sub_query() to add the separator as a constant column) but still no luck.</p>
  <p>So, what is the proper way to invoke <code>string_agg()</code> in SqlAlchemy?</p>