SQL Query how to count different value in single row

I am sure there must be a relatively straightforward way to do this, but it is escaping me at the moment. Suppose I have a SQL table like this:

+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |
+=====+=====+=====+=====+
|  a  |  b  |  3  | 100 |  << a,b
+-----+-----+-----+-----+
|  a  |  c  |  3  | 60  |  << a,c
+-----+-----+-----+-----+
|  开发者_如何学Goa  |  b  |  4  | 50  |  << a,b
+-----+-----+-----+-----+
|  a  |  b  |  5  | 30  |  << a,b
+-----+-----+-----+-----+
|  d  |  b  |  3  | 35  |  << d,b
+-----+-----+-----+-----+
|  a  |  c  |  2  | 40  |  << a,c
+-----+-----+-----+-----+

Now, I want to know how many times each combination of values for columns A and B appear, then it can hold data column D based on grouping by column C in single row. So, in this example, I want an output something like this:

+-----+-----+-----+-----+-----+
|  A  |  B  |  C3 |  C4 |  C5 |
+=====+=====+=====+=====+=====+
|  a  |  b  | 100 | 50  | 30  | << a,b
+-----+-----+-----+-----+-----+
|  a  |  c  | 60  |  0  |  0  | << a,c
+-----+-----+-----+-----+-----+
|  d  |  b  | 35  |  0  |  0  | << d,b
+-----+-----+-----+-----+-----+

What would be the SQL to determine that? I feel like this must not be a very uncommon thing to want to do.

Thanks!

---

You can use:

SELECT A, B,
       sum(if(C=3, D, NULL)) as C3,
       sum(if(C=4, D, NULL)) as C4,
       sum(if(C=5, D, NULL)) as C5
  FROM yourTable
GROUP BY A, B;

---

You can achieve a similar result by using GROUP_CONCAT (see docs), which gives you a comma-separated list of values in column D and column C and then extract your data by program.