SQL Query to Concatenate one column and have a bunch of other columns?

Question

Possible Duplicate:
Concatenate row values T-SQL

I have a table like this:

ref_num   name      type       route
----------------------------------------
1         A         W          401
2         B         X          401
3         C         E          401
3         C         E          411
4         D         Z          401
5         K         W          701
5         K         W          991
5         K         W          556
5         K         W          401
6         L         X          401
7         D         Y          401
7         D         Y          411
7         D         Y          680
8         E         Z          401

And i want a RESULT like this:

ref_num   name      type       routes
----------------------------------------
1         A         W          401
2         B         X          401
3         C         E          401,411
4         D         Z          401
5         K         W          701,991,556,401
6         L         X          401
7         D         Y          401,411,680
8         E         Z          401

If anybody can give me a query example to get the desired result it would be greatly appreciated

See: http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql, or This: http://ray.jez.net/concatenation-with-coalesce/ — David Manheim, Jun 27 '12 at 14:59

juergen d · Accepted Answer · 2012-06-27T15:11:25.553

3

select ref_num, name, type, group_concat(routes)
from your_table
group by ref_num

In MySQL you don't have to group by all values you are selecting. Other values will probably selected by random. But if the other values are the same in one group then you don't have to worry. That is the case in the OP's question.

edited Jun 27 '12 at 15:11

answered Jun 27 '12 at 14:56

juergen d

201,996
37
293
362

dont you need to group by name and type? – Diego Jun 27 '12 at 15:01
In the OP's example these values are always the same. – juergen d Jun 27 '12 at 15:02
@Diego: Yes and no. If all the table's rows are as the example - `name` and `type` depend on `ref_num`, no you don't have to. In MySQL and with the proper settings. – ypercubeᵀᴹ Jun 27 '12 at 15:04
1

yeah but it would be good to add it in case there are different ones. I didn't down voted you btw – Diego Jun 27 '12 at 15:04
Still, GROUP BY will require NAME and TYPE for this query. – PaddyC Jun 27 '12 at 15:05
1

@juergend: It would be good to explain when you have and when you don't have to. Or why you chose this one. – ypercubeᵀᴹ Jun 27 '12 at 15:05
1

@PaddyC: Not in MySQL (it isn't required as in other DBMS). But it may let the query show random results - in some cases. – ypercubeᵀᴹ Jun 27 '12 at 15:06
See this [example](http://sqlfiddle.com/#!2/d17ab/1) – juergen d Jun 27 '12 at 15:10
1

This behaviour (default in MySQL) can be disabled by setting sql_mode to [ONLY_FULL_GROUP_BY](http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by) – ypercubeᵀᴹ Jun 27 '12 at 15:14
@ypercube Ah, understood, thanks for clarification, my comment is Oracle specific. – PaddyC Jun 27 '12 at 15:28

score 3 · Answer 2 · answered Jun 27 '12 at 14:59

3

You can Concatenate them with GROUP_CONCAT with GROUP BY, something like:

SELECT ref_num, name, type, GROUP_CONCAT(routes SEPARATOR ', ')   
FROM TableNAme 
GROUP BY ref_num, name, type

answered Jun 27 '12 at 14:59

Mahmoud Gamal

78,257
17
139
164

score 0 · Answer 3 · answered Jun 27 '12 at 15:03

0

I think WM_CONCAT will do this:

SELECT REF_NUM, NAME, TYPE, WM_CONCAT(ROUTE) 
FROM ROUTES
GROUP BY REF_NUM, NAME, TYPE;

answered Jun 27 '12 at 15:03

PaddyC

576
7
14

Ah, apologies, WM_CONCAT is Oracle-specific. My bad :) Learn something every day... – PaddyC Jun 27 '12 at 15:07

SQL Query to Concatenate one column and have a bunch of other columns?

3 Answers3