Concat multiple row and column in single row mysql

Question

I have a table containing data in following format

id    |    name   |   age
----------------------------
1     |    John   |   24
2     |    Sam    |   NULL
3     |    Ron    |   32
4     |    Harry  |   44

now i want to fetch this into one row like

1:John:24,2:Sam,3:Ron:32,4:Harry:44

I have tried group_concat but it gives me only one columns values separated by comma, is it possible in mysql ?

maxtwoknight · Accepted Answer · 2013-05-09 11:52:50Z

4

Use group_concat and concat together:

SELECT group_concat(concat(id, ':', name, ':', IFNULL(age,''))) 
FROM table1

You can do this to get the ":" moved over

SELECT group_concat(concat(id, ':', name, IFNULL(concat(':',age),''))) 
FROM table1

And here's an updated SQLFiddle that hims056 created.

edited May 9, 2013 at 11:52

answered May 9, 2013 at 11:26

maxtwoknight

5,3763 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

maxtwoknight Over a year ago

But, this drops Sam (null age). Need to take that into account also.

Himanshu Over a year ago

@prashant - He has already updated. See this SQLFiddle

maxtwoknight Over a year ago

@prashant You might need to move the : into the IFNULL if you don't need it when age is null.

prashant Over a year ago

@xecaps12 how can I move : into IFNULL can you please elaborate little more

Rui Jarimba · Accepted Answer · 2013-05-09 11:40:58Z

0

Use this:

 select concat(id, ':',name, ':', age) as total_concated 
 from your_table 
 where 1

edited May 9, 2013 at 11:40

Rui Jarimba

18.5k11 gold badges64 silver badges101 bronze badges

answered May 9, 2013 at 11:23

Vijaya Pandey

4,2705 gold badges35 silver badges58 bronze badges

Collectives™ on Stack Overflow

Concat multiple row and column in single row mysql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related