FOR XML Syntac error UDB 16

[former_SQLA_member1695044]

I'm using Ultralite 16.0 and trying to aggregate multiple rows into one.

I have a film table and a genre table, that have a many-to-many relationship through the film_genre table.

I want a result like this:

| mFilm     | idGenre |
| --------- | ------- |
| Film_One  | 1, 2    |
| Film_Two  | 1, 3, 4 |

I can do this easily in SQL Server Management Studio (SSMS) with

SELECT f.nmFilm, 
(
  REPLACE(
      STUFF(
          (SELECT g.idGenre FROM genre g
              JOIN film_genre fg on g.idGenre = fg.idGenre
              WHERE fg.idFilm = f.idFilm
              FOR XML PATH('')
          ) , 1 , 1 , ''
      )
  , '&', '&')
) AS genres FROM film f;

However, any time I try to use FOR XML in SQLAnywhere such as in the query below

SELECT f.nmFilm, 
    (SELECT g.idGenre FROM genre g
      JOIN film_genre fg on g.idGenre = fg.idGenre
      WHERE fg.idFilm = f.idFilm
      FOR XML AUTO
    ) AS genres FROM film f;

I get syntax error

Could not execute statement. [UltraLite Database] Syntax error near 'XML' [SQL Offset: 57] SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

I can't find any reference to FOR XML in the documentation, so I'm unsure if this is available in version 16.0.

How could I achieve this?

[chris_keating]

FOR XML is not supported in an UltraLite SELECT statement.

[former_SQLA_member1695044]

Welp. It was a lot easier than I thought. Just had to use List(). Hope this helps someone else!

SELECT f.nmFilm, 
(SELECT LIST(g.idGenre) FROM genre g
  JOIN film_genre fg on g.idGenre = fg.idGenre
  WHERE fg.idFilm = f.idFilm
) AS genres FROM film f;

[VolkerBarth]

Yes, LIST() is a very helpful aggregate SQL Anywhere has had for times. MS SQL Server added a similar STRING_AGG aggregate function with SQL Server 17, AFAIK.

Note, it's often helpful to sort the entries in the list, say via

SELECT f.nmFilm, 
(SELECT LIST(g.idGenre ORDER BY g.idGenre) FROM genre g
  JOIN film_genre fg on g.idGenre = fg.idGenre
  WHERE fg.idFilm = f.idFilm
) AS genres FROM film f;

Additionally, as LIST is an aggregate function, you would usually use it with a GROUP BY, so you should easily be able to use a query without the need for a separate sub select in the SELECT clause.