2

I have two tables as below:

agents

id    |  name
1     |  abc
2     |  def

stores

id | name | agent_id
1  | aaa  | 1
2  | bbb  | 1

I want to result like this:

agent_name | store_name
  abc      | aaa|bbb

Not:

agent_name | store_name
  abc      |   aaa
  abc      |   bbb

Which query can help me do this? I know concat function but I don't know how to add characters | between strings.

Code Lღver
  • 15,573
  • 16
  • 56
  • 75
Greedy Man
  • 35
  • 6
  • Please show what you tried so far. Any SQL scripts? – ToBe Jan 23 '14 at 09:44
  • I think the following link might help you: http://stackoverflow.com/questions/15154644/sql-group-by-to-combine-concat-a-column – Menno Jan 23 '14 at 09:45

2 Answers2

4

You can use GROUP_CONCAT(expr)

SELECT 
  a.name,
  GROUP_CONCAT(s.name SEPARATOR '|') store_name
FROM
  agents a 
  JOIN stores s 
    ON (a.id = s.agent_id) 
GROUP BY a.id

Note Be aware of that the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

Try this

SELECT a.name agent_name,CONCAT(a.name,'|',b.name)store_name 
FROM agents a 
INNER JOIN stores b ON a.id=b.id

CONCAT function is used to concatenate two strings to form a single string. In this "name" from agents table is concatenated with a string '|' and "name" from stores table using join to get the required result

abinaya
  • 93
  • 9