OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Concatenating field values in query

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Kuzya59
General User
General User


Joined: 14 Feb 2010
Posts: 9

PostPosted: Tue Feb 16, 2010 2:41 am    Post subject: Concatenating field values in query Reply with quote

Hello! I have table with next content:
ID Street Number
1 Street1 1
2 Street2 2
3 Street2 35
4 Street1 5

I need make SQL-query who show me streets with concatenated house`s numbers:

Street1 1, 5
Street2 2, 35

Tell me please, how i can do that? In Google i founded only CONCAT() function, but query
Code:
SELECT CONCAT(Number) FROM table GROUP BY Street

don`t show me what i need. Have OO Mysql`s GROUP_CONCAT()-analog?
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Tue Feb 16, 2010 3:52 am    Post subject: Reply with quote

Hi there,

Download and have a read of the reportsfromscratch_forblog.pdf at the bottom of the following link, as it is a good read and mentions the principal of such things when working in the query designer... http://openoffice.blogs.com/openoffice/reports/

Basically it needs to be in the following form...

Code:
CONCAT( '(', CONCAT( "Street", CONCAT( ')  ', CONCAT( "Number" ) ) ) )


.. would create output like: (Street) 5

where as

Code:
CONCAT( "Street", CONCAT( '  ', CONCAT( "Number" ) ) )


... would create output like: Street 5

its sort of done in a cascaded manner. There is also a way to do it using the || symbol (above the \ symbol) which should work in open office versions after 2.41 I'm more comfortable using the above way, but perhaps someone else can detail the || method.

You just paste the concat statement in a new column in the query designer in the "field" field and then give it an alias.

Cheers,

Voo
Back to top
View user's profile Send private message
Kuzya59
General User
General User


Joined: 14 Feb 2010
Posts: 9

PostPosted: Thu Feb 18, 2010 2:07 am    Post subject: Reply with quote

Thanks you, but i don`t need that.
If i use your construction - CONCAT( "Street", CONCAT( ' ', CONCAT( "Number" ) ) )
then i get this result:
Quote:
Street1 1
Street1 5
Street2 2
Street2 35

But i need this:
Quote:
Street1 1, 5
Street2 2, 35

I think what CONCAT() can`t help me here because it concatenating only strings, who i give to it.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu Feb 18, 2010 5:21 am    Post subject: Reply with quote

I see now. Looks a lot harder than just a bit of concatination! Embarassed The query may need to be written in several stages for that sort of thing as it looks fairly complicated from checking out GROUP_CONCAT() equivalents with Google. Hopefully someone else can jump in and advise you if its possible to do with HSQL.

Voo
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group