0

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
2

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
  • Thanks for the response but I am using MySQL. So I have tried to use the equivalent GROUP_CONCAT. By using GROUP_CONCAT it concatenates the category column but reduces my 500 results into a single row. – gruuj Feb 28 '15 at 14:59
1

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;
0

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
0

The query:

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

works perfectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.