Skip to content
Advertisement

Concatenate 2 rows into one in SQL

I have a very simple data structure with just 3 tables:

  • requests
  • employees
  • teams

Basically, what I need is to concatenate 2 different SQL selects into one (so that the query could return just one row).

If there’s an employee associated with a request, then return their name (title) and concatenate it with ‘ / ‘.

Then, if there’s a responsible team for the request, return its title too.

Finally, concatenate these 2 properties into one

The best I could do is:

(SELECT CONCAT(e.title, ' / ') FROM employees AS e
    WHERE e.id = (SELECT r.resposible_employee_id FROM requests AS r WHERE r.id = 1))
UNION    
(SELECT t.title FROM teams AS t
    WHERE t.id = (SELECT r.responsible_team_id FROM requests AS r WHERE r.id = 1))

But it returns 2 rows instead of one

I could do the concatenation separately by querying the DB twice, but that’s not what I’m looking for. I want to query the DB just once.

For your convenience, here’s a SQL Fiddle

The query should return the below just as one row

John Doe / Johns team

Thanks in advance

Advertisement

Answer

Is something like the following what you’re after?

select 
    Concat_Ws(' / ',
        (select title from employees e where e.id=r.resposible_employee_id),
        (select title from teams t where t.id=r.responsible_team_id)
    )
from requests r 
where id=1

See Modified SQL Fiddle

Also suspect respo(n)sible_employee_id is a typo

2 People found this is helpful
Advertisement