SELECT LIST(attribute, ',')

Questions and answers regarding general SQL and backend databases

SELECT LIST(attribute, ',')

Postby huber » Thu Oct 26, 2023 3:14 pm

I assume the concatenating of all rows in a single list of values is not supported in Query Builder. Is that correct?

In SQL Anywhere, the code looks something like the following:

Code: Select all
-- Concatenate values from all city row names into single, comma delimited value
SELECT LIST(name, ', ') FROM cities;

I think, in SQL Server it's XML PATH instead of LIST, but I am not quite sure.

Best regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 514
Joined: Mon May 14, 2012 11:31 pm

Re: SELECT LIST(attribute, ',')

Postby patrick » Thu Oct 26, 2023 3:54 pm

With queryBuilder you should be able to basically call any (custom) function of a database by using

Code: Select all
q.result.add(q.functions.custom(YOURFUNCTION, [YOURFUNCTION_ARGS])


In SQL Server I usually use the function STRING_AGG for these things. A sample query could look like this (example is rather stupid):

Code: Select all
var q = datasources.db.northwind.orders.createSelect();

/** @type {QBJoin<db:/northwind/order_details>} */
var jOrdDetails = q.joins.add(datasources.db.northwind.order_details.getDataSource(), QBJoin.LEFT_OUTER_JOIN);
jOrdDetails.on.add(q.columns.orderid.eq(jOrdDetails.columns.orderid));

q.result.add(q.columns.orderid);
q.result.add(q.functions.custom('STRING_AGG', [jOrdDetails.columns.productid, '; ']), 'product_ids');

q.groupBy.add(q.columns.orderid);

var ds = databaseManager.getDataSetByQuery(q, -1);
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: SELECT LIST(attribute, ',')

Postby huber » Fri Oct 27, 2023 2:45 pm

Hi Patrick

Thanks for the code samples, it's very useful :-)

For one of the attributes, we have following SQL code. In QB, we don't find a way to implement the DISTINCT for the attribute "tc.untis_lesson_class".

Code: Select all
LIST (DISTINCT(tc.untis_lesson_class), ', ')

Any idea?
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 514
Joined: Mon May 14, 2012 11:31 pm

Re: SELECT LIST(attribute, ',')

Postby huber » Mon Oct 30, 2023 5:56 pm

Sorry, the code line does look like this and of course not as the one I posted:

Code: Select all
LIST (DISTINCT(tc.untis_lesson_class), ', ' ORDER BY tc.untis_lesson_class)

Any idea?
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 514
Joined: Mon May 14, 2012 11:31 pm

Re: SELECT LIST(attribute, ',')

Postby rgansevles » Fri Nov 03, 2023 10:35 am

Hi Robert,

The custom function can be nested as well, try something like this:
Code: Select all
q.result.add(q.functions.custom('LIST', [q.functions.custom('DISTINCT', [q.columns.untis_lesson_class]), '; ']))


Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1926
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: SELECT LIST(attribute, ',')

Postby huber » Tue Nov 07, 2023 7:31 pm

Thanks Rob. The SQL code part

Code: Select all
LIST (DISTINCT(tr.untis_assigned_room), ', ')

translated to QB as

Code: Select all
.add(query.functions.custom('LIST', query.functions.custom('DISTINCT', tr.untis_assigned_room), ', '))

works perfectly.

What I can't translate is

Code: Select all
LIST (DISTINCT(tr.untis_assigned_room), ', ' ORDER BY tr.untis_assigned_room)

Tried for example

Code: Select all
.add(query.functions.custom('LIST', query.functions.custom('ORDER BY', tr.untis_assigned_room), ', ')).distinct = true

but get the error "Ein Ausnahme-Fehler ist aufgetreten: 217 java.lang.IllegalArgumentException: Not a valid sql name: ORDER BY"


Any idea how to add the ORDER BY part to the LIST function in QB?
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 514
Joined: Mon May 14, 2012 11:31 pm

Re: SELECT LIST(attribute, ',')

Postby rgansevles » Fri Nov 10, 2023 2:03 pm

Hi Robert,

I guess you are hitting the limits of what you can do with the custom function.
If you do without the order-by, can you sort afterwards in your solution?
Maybe in a column converter?

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1926
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: SELECT LIST(attribute, ',')

Postby huber » Fri Nov 10, 2023 2:15 pm

Hi Rob

Thanks for the idea, which I will try later on. For the moment, I leave the original SQL as it is - it works fine.
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 514
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: Bing [Bot] and 3 guests