MySQL Query SELECT multiple rows

Question

I have the following table:

surveys
comp_id    question
4          What is your name?
4          How are you?
4          Where do you live?
5          Who are you?
5          What is your birthday?

I need help writing a Query that gives me the following output:

comp_id    my_questions
4          What is your name?How are you?Where do you live?
5          Who are you?What is your birthday?

Thanks,

group_concat(). but why do you want to 'destroy' your text like this? — Marc B
– Marc B, Commented Dec 13, 2012 at 20:47

Mike Brant · Accepted Answer · 2012-12-13 20:58:21Z

2

You are looking for the GROUP_CONCAT() function. Use it like this:

SELECT comp_id, GROUP_CONCAT([DISTINCT] question [ORDER BY some_field ASC/DESC] [SEPARATOR '']) as my_questions
FROM surveys
GROUP BY comp_id

Note I have shown some some optional values to pass into GROUP_CONCAT in []. To get exact like you are showing just use GROUP_CONCAT(question SEPARATOR ''). The optional items let you look for distinct question values or order them by any field(s) (including question itself).

edited Dec 13, 2012 at 20:58

answered Dec 13, 2012 at 20:49

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

6 Comments

Sohel Mansuri Over a year ago

I'm doing the following query but it doesn't seem to work: "SELECT GROUP_CONCAT(question) AS Questions FROM surveys WHERE question_code = 1 AND question_code = 2"

Mike Brant Over a year ago

You need a GROUP BY clause on some field. I am actually surprised this didn't produce MySQL error as most aggregate functions do when no GROUP BY is present.

Sohel Mansuri Over a year ago

so if I put 'GROUP BY comp_id' without selecting comp_id, it will work?

Mike Brant Over a year ago

Yes, you don't need to SELECT a field in order to use GROUP BY on it.

Mike Brant Over a year ago

This is because you are not selecting any records. How can a record have question_code = 1 AND question_code = 2. A single record can have one value or the other, not BOTH. You probably need to use WHERE question_code IN (1,2) or WHERE question_code = 1 OR question_code = 2. The first is probably better code for dynamic queries where number of values of question_code that you are filtering on might vary.

|

Collectives™ on Stack Overflow

MySQL Query SELECT multiple rows

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related