| View previous topic :: View next topic |
| Author |
Message |
Kuzya59
General User
Joined: 14 Feb 2010
Posts: 9
|
 Posted: Tue Feb 16, 2010 2:41 am Post subject: Concatenating field values in query |
 |
|
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 |
|
 |
Voobase
OOo Advocate
Joined: 21 Nov 2007
Posts: 400
Location: Australia
|
| Posted: Tue Feb 16, 2010 3:52 am Post subject: |
|
|
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 |
|
|
Kuzya59
General User
Joined: 14 Feb 2010
Posts: 9
|
| Posted: Thu Feb 18, 2010 2:07 am Post subject: |
|
|
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 |
|
|
Voobase
OOo Advocate
Joined: 21 Nov 2007
Posts: 400
Location: Australia
|
| Posted: Thu Feb 18, 2010 5:21 am Post subject: |
|
|
I see now. Looks a lot harder than just a bit of concatination!  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 |
|
|
|
