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
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? asked 10 Jul '22, 19:31 almonddaves |
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; answered 10 Jul '22, 19:32 almonddaves 1
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.
(11 Jul '22, 03:16)
Volker Barth
|
FOR XML is not supported in an UltraLite SELECT statement.