Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 10 Jul '22, 19:31

almonddaves's gravatar image

almonddaves
26113
accept rate: 0%

FOR XML is not supported in an UltraLite SELECT statement.

(10 Jul '22, 23:58) Chris Keating

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;
permanent link

answered 10 Jul '22, 19:32

almonddaves's gravatar image

almonddaves
26113
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×162
×29

question asked: 10 Jul '22, 19:31

question was seen: 555 times

last updated: 11 Jul '22, 03:21