0

I don't understand how I could concatenate the results of several rows into one.

Update :

The solution should be easily exportable to the main database servers (MYSQL, postgresql, sql server, oracle..)

Context :

Table 1 :

id_core int(11) UNSIGNED
definition varchar(32) utf8_general_ci

Table 2 :

id_core int(11) UNSIGNED
engine_type varchar(32) utf8_general_ci

Here is an example of how they are filled :

Table 1 :

1 blabblah
1 blahblah second / different component
1 blahblah third / different component
2 other stuffs
2 other stuffs on another component

Table 2 :

1 engine type1
2 engine_type2

What do I currently get :

select * from table1 
join table2
on table1.id_core = table2.id_core
where table1.id_core = 1

And it leads to :

1 - engine_type1 - blabblah
1 - engine_type1 - blahblah second / different component
1 - engine_type1 - blahblah third / different component

Now, I would like to reduce the number of rows retrieved and get :

1 - engine_tyoe1 - blabblah - blahblah second / different component - blahblah third / different component

I would then get only one row instead of three and this rows already has all the attributes in it.

Is it even possible ?

Thanks

Community
  • 1
  • 1
Larry
  • 1,735
  • 1
  • 18
  • 46
  • Please check this out: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – Ruud Helderman Jan 12 '14 at 09:17
  • @Ruud Isn't the solution there SQL Server specific? This here looks like MySQL to me. – peter.petrov Jan 12 '14 at 09:22
  • 1
    @peter.petrov: notice the first comment: "This type of problem is solved easily on MySQL with its GROUP_CONCAT() aggregate function..." – Ruud Helderman Jan 12 '14 at 09:34
  • It works on mysql but then it has to work on several database with minimal code change. I update the question. – Larry Jan 12 '14 at 09:38
  • in Postgres, you can use `string_agg()`, in Oracle `listag()`. For SQL Server solutions search for `group_concat equivalent in sql server` here on SO. This is being asked about once a week. –  Jan 12 '14 at 09:52
  • Please take one step back and ask yourself *why* you want this; is there a use case, or is your question academic/hypothetical? In relational databases, it is uncommon to organize your rows into columns; that's why there is no standard solution that works across all different database engines. Naturally, you may have the need to *present* your data in a matrix, but that is the responsibility of the presentation layer; not the query engine. – Ruud Helderman Jan 12 '14 at 09:53
  • I think you are right, let's give up on doing this at the engine level.. – Larry Jan 12 '14 at 09:54
  • @Larry - it gets worse - some RDBMSs don't have any equivalent aggregating function (either at all, or available), so the entire structure of the query has to change. There is **no** universal solution (even ignoring swapping out function names) at the db level. And in the future, please look around more; as others have noted, this question is on here (multiple times) already, for every implementation. – Clockwork-Muse Jan 12 '14 at 10:30

0 Answers0