sorting a table by index

[rotemblu]

I have a table with answered questions that looks like that:
1 answer 1
1 answer 2
1 answer 3
1 answer 4
.
.
.
2 answer 1
2 answer 2
2 answer 3
2 answer 4
.
.
ect
and I want it to transform it to a table like this:

answers 1 answers 2 answers 3 answers 4 ...
1 | answer 1 | answer 2 | answer 3 | answer 4 | ...
2 | answer 1 | answer 2 | answer 3 | answer 4 | ...
.
.

note that I want the answers to be on different columns thus:

SELECT number_column
   , GROUP_CONCAT( answer_column SEPARATOR ' ' ) answer_column
FROM your_table
GROUP BY number_column
ORDER BY number_column

isn't working for me beacuse I get all the answers connected as one string in one column

thanks!!!
rotem blumberg

[pbmods]

Heya, Rotem. Welcome to TSDN!

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

The problem with the situation you're describing is that it only works if there are always 4 or less answers per question. But the whole point of storing the values the way you did is so that any question can have an unlimited number of answers.

If you need to generate a report in that format, consider loading the results in a scripting language such as PHP and formatting your report that way.