How does one reference the currently-grouped by field in a subquery?
Consider a table with the following data:
Client Status Size
1 A 500
1 B 1500
2 A 2000
2 B 800
I am trying to get the following data in a single query:
Client A B
1 500 1500
2 2000 800
I've been playing with variations on this, with absolutely no success:
SELECT
t.Client,
(
SELECT A FROM table WHERE Client=t.Client
) as A,
(
SELECT B FROM table WHERE Client=t.Client
) as B
FROM table t
GROUP BY t.Client;
The status for each Client is not repeated, that is, each client should have only one row with a given status. For purpose of this question, we can assume that there is exactly one entry for each status.
Note that there are about one hundred thousand rows: about 20 thousand Clients and five status per client. There exist other fields as well (such as date of status update and the user who performed the update) so changing the table schema to simply Client, A, B
is not feasible.
How can I reference the grouped field in the subqueries?