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?
As mentioned by @jarlh you could do this using group_concat
SELECT user_id,GROUP_CONCAT(workplace)
FROM yourtable
GROUP BY user_id;
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)
