按字段分组并显示其他字段

Question

I have the following sql table structure (table name 'my_table'):

**ID - USER_ID - FIELD_ID - VALUE**

1 - 1 - 1 - letter a

2 - 1 - 2 - letter b

3 - 1 - 3 - letter c

4 - 2 - 1 - letter a

5 - 2 - 2 - letter b

Then, I would like to display the results in an html table like so:

**USER_ID - FIELD_ID1->value - FIELD_ID2->value - FIELD_ID3->value**

1 - letter a - letter b - letter c

2 - letter a - letter b

Any takers?? Thanks :)

EDIT:

Here is a fiddle: http://sqlfiddle.com/#!2/bd980/5

It is the bottom table I am attempting to display results from.

Answer 1

This should work:

SELECT t.user_id,
       MAX(IF(t.field_id = 1, t.value, NULL)) AS key1,
       MAX(IF(t.field_id = 2, t.value, NULL)) AS key2,
       MAX(IF(t.field_id = 3, t.value, NULL)) AS key3
FROM my_table t
GROUP BY t.user_id