0

Let's assume I have two tables (A and B):

A:
|   ID   | column1   |   B_ID   |  column2 | ... | column x |
|   1    | xxx       |   25     | xxxx...

B:
|  ID    | B_ID |  C    |  column 1  | ...   | column x|
|  2     | 25   |   55  |   xxxxxxx
|  3     | 25   |   66  |   xxxxxxx (data in all other columns are the same,

only C column differs) and I do inner join on them (using B_ID).

Because in table B there are two matching rows for inner join, I get two rows as a result. This happens even when using DISTINCT because data in column C differs. Is there some way to somehow join differing values of C in new value (like "55,66") or to put C value 66 in another column (like C_1) so I can have only one row as a result?

Tians
  • 443
  • 1
  • 5
  • 14
  • Always try to include your rdbms tag for sql questions `SqlServer`, `MySql`?. Some functions arent available on all databases. – Juan Carlos Oropeza Nov 05 '15 at 15:02
  • Possible duplicate of [Is there any function in oracle similar to group\_concat in mysql?](http://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-to-group-concat-in-mysql) There you have sql server, mysql and oracle versions – Juan Carlos Oropeza Nov 05 '15 at 15:06

2 Answers2

0
SELECT *
FROM
A T1 INNER JOIN
(SELECT ID,B_ID,GROUP_CONCAT(C),column1,column2,column3,..,columnx
FROM B 
GROUP BY B_ID) T2 ON T2.B_ID=T1.B_ID

Hope this helps..

Subin Chalil
  • 3,531
  • 2
  • 24
  • 38
0

depending on the SQL version you're using: Oracle - look at STRING_AGG() function on asktom.com if prior to Oracle 11G, built-in as LISTAGG() afterwards. mysql- look at group_concat() sql server - Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Community
  • 1
  • 1
gadaju
  • 316
  • 2
  • 4