-1

Possible Duplicate:
Concatenate many rows into a single text string?

MY SQL : I have a table that has 3 columns:

╔═════╦═════╦════════╗
║ ID1 ║ ID2 ║ ANSWER ║
╠═════╬═════╬════════╣
║   1 ║   2 ║      4 ║
║   1 ║   2 ║      5 ║
║   1 ║   4 ║      8 ║
╚═════╩═════╩════════╝

And i want a table that will look like that:

╔═════╦═════╦════════╗
║ ID1 ║ ID2 ║ ANSWER ║
╠═════╬═════╬════════╣
║   1 ║   2 ║ 4 + 5  ║
║   1 ║   4 ║ 8      ║
╚═════╩═════╩════════╝

Meaning: if i have different answers for id1&id2, i want them to be in one row with some separator (it can be + or something else) and only one row for each pair of id1,id2

Community
  • 1
  • 1
Alexandra Orlov
  • 293
  • 2
  • 4
  • 12
  • 3
    What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `SQL Server`? `MySQL`? `Oracle`? `DB2`? etc.. – John Woo Feb 04 '13 at 14:37

2 Answers2

1

In MySQL you can use the GROUP_CONCAT() function with a GROUP BY to get the Answer values concatenated into the same row:

select id1,
  id2,
  group_concat(answer SEPARATOR ' + ') as Answer
from yourtable
group by id1,  id2

See SQL Fiddle with Demo.

This produces the result:

| ID1 | ID2 | ANSWER |
----------------------
|   1 |   2 |  4 + 5 |
|   1 |   4 |      8 |
Taryn
  • 242,637
  • 56
  • 362
  • 405
1

Since you have mentioned now that you are using MySQL, you can take advantage of GROUP_CONCAT.

SELECT ID1, ID2, GROUP_CONCAT(Answer SEPARATOR ' + ') AnswerList
FROM tableName
GROUP BY ID1, ID2
John Woo
  • 258,903
  • 69
  • 498
  • 492