pivot query doesn't work

Question

I'm trying for the first time in my life to use the PIVOT on my database table. This is the table:

Answers (id_user, id_question, topic, value) VALUES
(1, 1, 'a', 3),
(1, 1, 'b', 3),
(1, 1, 'c', 2),

(1, 2, 'a', 1),
(1, 2, 'b', 2),
(1, 2, 'c', 3),

[...]

(1, 14, 'a', 1),
(1, 14, 'b', 2),
(1, 14, 'c', 1);

But I'm trying to get to this:

Answers (id_user, id_question, topic_A, topic_B, topic_C) VALUES
(1, 1, 3, 3, 2),
(1, 2, 1, 2, 3),
[...]
(1, 14, 1, 2, 1);

with this query I get a generic syntax ERROR near PIVOT

SELECT *
FROM
(     SELECT id_user, id_question, topic, value
      FROM Answers
      WHERE id_user=98
) as risp
PIVOT 
(     FOR id_question IN ([a],[b],[c])
)     AS pvt

@MatteoTassinari, right - the `PIVOT` syntax is for Microsoft SQL Server, and it's not supported by other SQL implementations. — Bill Karwin, Jan 28 '14 at 13:24
there's other approach to do this? edit: thank you for the answer, I'm watching the post you have linked — user3160178, Jan 28 '14 at 13:29

score 0 · Accepted Answer · answered Jan 28 '14 at 13:32

One way todo this in MySQL using aggregation:

select id_user, id_question,
       max(case when topic = 'A' then value end) as topic_A,
       max(case when topic = 'B' then value end) as topic_B,
       max(case when topic = 'C' then value end) as topic_C
from Answers a
group by id_user, id_question;

By the way, it wouldn't be reasonable to put this back into the same table. The query should be sufficient. You can also put this into a view or into another table.

EDIT:

An alternative approach is to use joins. The query is a bit more complicated:

select uq.id_user, uq.id_question, aa.value as topic_A,
       ab.value as topic_B, ac.value as topic_C
from (select distinct id_user, id_question from answers) uq left outer join
     Answers aa
     on aa.id_user = uq.id_user and aa.id_question = uq.id_question and
        aa.topic = 'A' left outer join
     Answers ab
     on ab.id_user = uq.id_user and ab.id_question = uq.id_question and
        ab.topic = 'B' left outer join
     Answers aa
     on ac.id_user = uq.id_user and ac.id_question = uq.id_question and
        ac.topic = 'C';

the first query works great, instead the second doesn't work. no result, phpmyadmin reloads the page without show nothing. however, the first it's perfect! Thank you!! — user3160178, Jan 28 '14 at 13:58

pivot query doesn't work

1 Answers1

Linked