CODEWITHSUNDEEP
mysqlsql
HimanAB
HimanAB

Reputation: 2573

put some column values in a new column using sql

I have table like this

user_id    workplace
1          Microsoft
1          Google
2          eBay
3          Panadora
3          Netflix

What I want is to have a table like this:

user_id   places_worked
1        Microsoft,Google
2        eBay
3        Panadora,Netflix

Is there anyway in SQL that can do this?

Upvotes: 0

Views: 23

Answers (2)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

you can use group by with group concat operation

SELECT user_id,GROUP_CONCAT(workplace) FROM yourtable GROUP BY user_id;

check following example

select * from payments;
+----+------------+---------+-------+
| id | date       | user_id | value |
+----+------------+---------+-------+
|  1 | 2016-06-22 |       1 |    10 |
|  2 | 2016-06-22 |       3 |    15 |
|  3 | 2016-06-22 |       4 |    20 |
|  4 | 2016-06-23 |       2 |   100 |
|  5 | 2016-06-23 |       1 |   150 |
+----+------------+---------+-------+
5 rows in set (0.00 sec)


select c.user_id,group_concat(p.value) from calls c inner join payments p on p.user_id=c.user_id group by c.user_id;
+---------+-----------------------+
| user_id | group_concat(p.value) |
+---------+-----------------------+
|       1 | 10,150,10,150,10,150  |
|       2 | 100                   |
+---------+-----------------------+
2 rows in set (0.00 sec)

Upvotes: 0

cableload
cableload

Reputation: 4375

As mentioned by @jarlh you could do this using group_concat

 SELECT user_id,GROUP_CONCAT(workplace)
    FROM yourtable
    GROUP BY user_id;

Upvotes: 1

Related Questions