Apache OpenOffice Community Forum

Hello,

I have a rather tricky problem.

I have a dataset such as:

I need to create a simple query (that i can hopefully use in a simple form) that will sort it by it by Section, then by ID and concatenate the Descriptions together. Like this:
I tried many different things, including CONCAT command, but I still cannot figure out how to make it join rows into a single row and how to sort it, all at the same time.

If anyone can help me, I would really appreciate it.

Thank you.

What you are describing is called GROUP_CONCAT Aggregate Function.

Please see the post below . . . starting with the post(s) by Sliderule . . . and . . . the GRAPHICS.

http://www.oooforum.org/forum/viewtopic.phtml?t=104415

Per your description, the SQL Query code would be:
Explanation : This feature is NOT available as is with the default OpenOffice Base HSQL Engine ( Embedded database version 1.8 ) .

It is available, if you elect to upgrade your database engine to HSQL 2.2.4 ( that is the current version available for download as HSQLDB.JAR file as the day I am writing this.

Please see the posts on this forum by DACM, he has available the technique and tools to migrate to HSQL 2.2.4 .

See DACM's post at: http://user.services.openoffice.org/en/ ... 83&t=17567

I hope this helps.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Just want to add another LINK . . . from DACM . . . with what he calls "Quick Fix" for Automatically create a new HSQLDB 2.x "multi-mode" database

http://www.oooforum.org/forum/viewtopic ... 523#396523

Sliderule

Thanks, Sliderule. It works.

I did know about Group_Concat and this SQL upgrade, but I was hoping that I can avoid it and I was also hoping that I simply did not know how to do it right ( ).

Ohh, well... Maybe normal front/back-end DB is better anyway. Although I was extremely surprised that built in SQL (HSQL 1.8) did not support this. Its a basic function pretty much. Makes me wonder what else it does not support

Just a note: I used this link (from the one that you have provided) to migrate single file DB: http://www.oooforum.org/forum/viewtopic ... 995#386995
But I have to say that this migration is not exactly user friendly. Also, it does not mention SHUTDOWN COMPACT command to make exported data work faster.

Anyway, thanks again for the response and nice ready-to-use query (saved me some time).

Great that things work as you needed.

You said . asked:

Its a basic function pretty much. Makes me wonder what else it does not support

The HSQL 2.2.4 documentation for SQL functions can be found at:

http://www.hsqldb.org/doc/2.0/guide/bui ... chapt.html

A few of the functions that are in HSQL 2.2.4 but NOT in 1.8 include

1. Date arithmetic: DATEADD
2. Paddding - LPAD, RPAD
3. Regular Expression - REGEXP_MATCHES
4. Change characters backwards - REVERSE
5. Aggregate Functions - GROUP_CONCAT and MEDIAN
6. Ability to write your own functions, for example, I wrote a PROPER function to start each word in a string with an UPPER case character and all other characters lower case, as well as writing a RANDBETWEEN function

Sliderule

Thanks, man. I was just braggin a bit

I mean, of all 100s of functions, it did not have the one I needed. lol

But seriously, I do appreciate a lot the tremedous work that openoffice community does.