SQL - create pivot table/cross tab from raw data

Question

What is the best way to transform the layout of a flat table into a crosstab/pivot table with SQL.

there is no need to calculations. The first column of the raw table will be the first column in PV table, second colum of raw will be spitted in columns (12 distinct values) in PVtable. Values will be then the third column in raw table.

I am having difficulties to transform this layout and I guess I am putting to much cases for this making it hard to read/maintain.

does anyone has an idea how to do it? Many thanks!

Example, below RAW:

indx rank score
1   1   59
1   2   15
1   3   17
1   4   7
1   5   56
1   6   13
1   7   7
1   8   3
1   9   7
1   10  10
1   11  2
1   12  181
2   2   16
2   3   19
2   4   7
2   5   79
2   6   20
2   7   13
2   8   5
2   9   10
2   10  18
2   11  5
2   12  268
3   3   12
3   4   6
3   5   56
3   6   10
3   7   9
3   8   5
3   9   8
3   10  17
3   11  3
3   12  219
4   4   1
4   5   19
4   6   4
4   7   3
4   8   2
4   9   6
4   10  5
4   11  1
4   12  102

PVtable:

                        Rank                                
indx 1  2   3   4   5   6   7   8   9   10  11  12
1   59  15  17  7   56  13  7   3   7   10  2   181
2   -   16  19  7   79  20  13  5   10  18  5   267
3   -   -   12  6   56  10  9   5   8   17  3   219
4   -   -   -   1   19  4   3   2   6   5   1   101
5   -   -   -   -   0   0   0   0   0   0   0   0
6   -   -   -   -   -   0   0   0   0   0   0   0
7   -   -   -   -   -   -   0   0   0   0   0   0
8   -   -   -   -   -   -   -   0   0   0   0   0
9   -   -   -   -   -   -   -   -   0   0   0   0
10  -   -   -   -   -   -   -   -   -   0   0   0
11  -   -   -   -   -   -   -   -   -   -   0   0
12  -   -   -   -   -   -   -   -   -   -   -   0

Answer 1

A canonical way that works in just about any database is the conditional aggregation approach:

select indx
       max(case when rank = 1 then score end) as rank1,
       max(case when rank = 2 then score end) as rank2,
       . . .
       max(case when rank = 12 then score end) as rank12
from table t
group by indx
order by indx;

In your example, though, it is unclear what indexes of 5 and more all have values of 0.

EDIT:

If you want the values of 5+ to be 0 and to get values from 1-2, then use a left outer join and change the logic in the max():

select n.n as indx,
       max(case when rank = 1 then score end) as rank1,
       max(case when rank = 2 then score end) as rank2,
       . . .
       max(case when rank = 12 then score when n.n >= 12 then 0 end) as rank12
from (select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12
     ) n left outer join
     table t
     on n.n = t.indx
group by n.n
order by n.n;