mysql result format into single row

Question

Query-

select count(*) Jobs, partners.name Partner 
from jobs,customers, partners 
where customers.partner_id = partners.id 
and jobs.customer_id = customers.u_key 
and (jobs.status=100 or jobs.status=8) 
and jobs.created_at between '2016-02-08 00:00:00' and '2016-02-09 00:00:00'    
group by customers.partner_id;

The result of my sql query are like

+------+----------+
| Jobs | Partner  |
+------+----------+
|    2 | Peter    |
|    2 | POLO     | 
|   24 | Pilot    |
+------+----------+

How do I format it in mysql to show the result like 2 Peter, 2 POLO, 24 Pilot. Is it Possible?

Possible duplicate of [how to concat two columns into one with the existing column name in mysql?](http://stackoverflow.com/questions/20284528/how-to-concat-two-columns-into-one-with-the-existing-column-name-in-mysql) — Sully, Feb 10 '16 at 05:40
Do you really want a single line of output, or do you want multiple records with concatenated columns? — Tim Biegeleisen, Feb 10 '16 at 05:41
@ Tim Biegeleisen I will need a single line out put, as I am using a api to automate a chat message which sends a message every 3 hours and that needs to be sent in a single line so that it doesn't look like big chat message. — Uppi, Feb 10 '16 at 05:43

score 1 · Answer 1 · answered Feb 10 '16 at 05:39

1

Yes, This is possible. All you need to do is combine both column using concat function.

Here is the way to do that:

select concat(jobs,' ',partner);

answered Feb 10 '16 at 05:39

Abhishek Ginani

4,511
4
23
35

Ullas · Accepted Answer · 2016-02-10T17:34:44.840

1

Use CONCAT and GROUP_CONCAT.

Query

SELECT GROUP_CONCAT(CONCAT(Jobs, ' ', Partner) separator ',') AS new_column
FROM my_table;

Result

+-------------------------+
| new_column              |
+-------------------------+
| 2 Peter,2 POLO,24 Pilot |
+-------------------------+

SQL Fiddle Demo

`Edit:`

Query

select  GROUP_CONCAT(CONCAT(t.Jobs, ' ', t.Partner) separator ',') as new_column from
(
    select count(*) Jobs, partners.name Partner 
    from jobs,customers, partners 
    where customers.partner_id = partners.id 
    and jobs.customer_id = customers.u_key 
    and (jobs.status=100 or jobs.status=8) 
    and jobs.created_at between '2016-02-08 00:00:00' and '2016-02-09 00:00:00'    
    group by customers.partner_id
)t;

edited Feb 10 '16 at 17:34

answered Feb 10 '16 at 05:51

Ullas

11,450
4
33
50

select GROUP_CONCAT(CONCAT(count(*) Jobs,' ', partners.name) separator ',' ) as Partner from jobs,customers, partners where customers.partner_id = partners.id and jobs.customer_id = customers.u_key and (jobs.status=100 or jobs.status=8) and jobs.created_at between '2016-02-08 00:00:00' and '2016-02-09 00:00:00' group by customers.partner_id; I get Incorrect parameters in the call to native function 'CONCAT – Uppi Feb 10 '16 at 05:58
Awesome Ullas. Thanks – Uppi Feb 10 '16 at 07:01

mysql result format into single row

2 Answers2

Edit:

`Edit:`