Combining multiple rows into single column

Question

The return from my MySQL statement displays an example below:

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person
001  | open   | 011  | john | male

How would I combine the multiple rows to the category column? Like the example below.

id 1 | status | id 2 | name | category
001  | open   | 011  | john | person, male

Answer 1

Below is what you need

CREATE TABLE customers
    ([id 1] int, [status] varchar(4), [id 2] int, [name] varchar(4), [category] varchar(6))
;

INSERT INTO customers
    ([id 1], [status], [id 2], [name], [category])
VALUES
    (001, 'open', 011, 'john', 'person'),
    (001, 'open', 011, 'john', 'male')
;
select * from dbo.customers

---- You have to use STUFF function with FOR XML PATH

 select distinct cs.[id 1]
    ,cs.[status]
    ,cs.[id 2]
    ,cs.[name]
    ,stuff((
            select ',' + c.[category]
            from dbo.customers c
            where cs.[id 1] = c.[id 1]
            for xml path('')
            ), 1, 1, '') as [category]
     from dbo.customers cs

Answer 2

In Mysql query should be like

select `id 1`,`status`,`id 2`,`name`,group_concat( category order by `id 1` asc SEPARATOR ',') as category 
from customer;

Answer 3

Try to use GROUP_CONCAT and then GROUP_BY your id.

SELECT t.id1, t.status, t.id2, t.name, GROUP_CONCAT(t.category) category
FROM yourtable t
WHERE (t.name = 'john' or t.name = 'othername')
GROUP BY t.id1

Answer 4

The query:

SELECT t.user_id, GROUP_CONCAT(t.search_text) category 
FROM tblusersearches t 
GROUP BY t.user_id

works perfectly.