sql: long to wide format without using PIVOT

I have a table like the following:

user_id time_id val0 val1 val2 actual_value score
1       1       1    0     0   0           0.6
1       1       0    1     0   1           0.4
1       1       0    0     1   2           0.3
1       2       1    0     0   0           0.7
1       2       0    1     0   1           0.4
1       2       0    0     1   2           0.3
2       1       1    0     0   0           0.9
2       1       0    1     0   1           0.5
2       1       0    0     1   2           0.4

I want to convert the data to wide format like the following:

user_id time_id  score_0 score_1 score_2
1       1           0.6.    0.3.    0.3
1       2           0.7.    0.4.    0.3
2       1           0.9.    0.5.    0.4

the SQL I used does not have a pivot choice so I am wondering how to convert the long format to wide without using PIVOT.

Answer

If I understand your question correctly, you can do conditional aggregation:

select
    user_id,
    time_id,
    max(case when val0 = 1 then score end) score0,
    max(case when val1 = 1 then score end) score1,
    max(case when val2 = 1 then score end) score2
from mytable
group by user_id, time_id

Maybe you want to use actual_value to pivot instead of the val[n] columns:

select
    user_id,
    time_id,
    max(case when actual_value = 0 then score end) score0,
    max(case when actual_value = 1 then score end) score1,
    max(case when actual_value = 2 then score end) score2
from mytable
group by user_id, time_id