Mysql converting rows to columns and grouping by response. -

Mysql converting rows to columns and grouping by response. -

i have 4 different tables used gather info user, , i'm trying write query simplify output responses can viewed single row. tables setup so:

surveys

+-----+-----------+ | sid | stitle | +-----+-----------+ | 1 | survey 1 | | 2 | survey 2 | +-----+-----------+

entries

+-----+-----+ | eid | sid | +-----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +-----+-----+

questions

+-----+-----+------+------------+ | qid | sid | type | question | +-----+-----+------+------------+ | 1 | 1 | text | question | | 2 | 1 | text | question b | | 3 | 2 | text | remark | | 4 | 2 | text | remark b | +-----+-----+------+------------+

entry responses

+-----+-----+-----+-------------+ | rid | eid | qid | response | +-----+-----+-----+-------------+ | 1 | 1 | 1 | yes | | 2 | 1 | 2 | no | | 3 | 2 | 1 | maybe | | 4 | 2 | 2 | no | | 5 | 3 | 1 | foo | | 6 | 3 | 2 | bar | | 7 | 4 | 3 | reply | | 8 | 4 | 4 | unicorns | +-----+-----+-----+-------------+ etc...

and have query displays them such:

+-----+----------+-----+------------+------------+ | sid | stitle | eid | question | question b | +-----+----------+-----+------------+------------+ | 1 | survey 1 | 1 | yes | no | | 1 | survey 1 | 2 | maybe | no | | 1 | survey 1 | 3 | foo | bar | +-----+----------+-----+------------+------------+

and / or

+-----+----------+-----+------------+------------+ | sid | stitle | eid | remark | remark b | +-----+----------+-----+------------+------------+ | 2 | survey 2 | 4 | reply | unicorns | +-----+----------+-----+------------+------------+

depending on survey queried limiting factor. essentially, people responded survey , answers questions can different depending on survey. have questions asked per survey column header, , answer's in rows.

i'm doing long joins , query's each individual response per question, , manually adding row i'm hoping there's improve way it.

mysql