Manipulating result on a SELECT query

[avataru]

I have the following table:

+-------------------------+
| id | tid | type | value |
+-------------------------+
| 1  | 23  | 1    | 8     |
| 2  | 21  | 1    | 6     |
| 3  | 21  | 2    | 4     |
| 4  | 21  | 2    | 9     |
| 5  | 22  | 2    | 9     |
| 6  | 22  | 1    | 10    |
| 7  | 23  | 1    | 5     |
| 8  | 22  | 1    | 4     |
| 9  | 21  | 2    | 7     |
| 10 | 22  | 2    | 7     |
+-------------------------+

and I want to get the result like this:

+-----------------------+
| tid | value1 | value2 |
+-----------------------+
| 21  | 6.0    | 6.6667 |
| 22  | 7.0    | 8.0    |
| 23  | 6.5    | NULL   |
+-----------------------+

where value1 is the average of all the values for a tid and type = 1

Is it possible to do this? The closest I got is to use UNION on 2 queries, 1 for each type but then all the values will be on one column but that isn't useful in my case.

+---------------------+
| tid | type | value  |
+---------------------+
| 21  | 1    | 6.0    |
| 21  | 2    | 6.6667 |
| 22  | 1    | 7.0    |
| 22  | 2    | 8.0    |
| 23  | 1    | 6.5    |
+---------------------+

Thank you in advance

[mbesuma]

I dont exactly understand what you are trying to get at. You want the average of values on a given tid where type=1?

[Lars_Berg]

select tid ,
 avg(case when type = 1 then value + 0.0 end) as value1,
 avg(case when type = 2 then value + 0.0 end) as value2
from t
group by tid

[avataru]

Thank you Lars, it worked. I didn't know about CASE