How to transpose a table in SQLite?

No need to use windowing functions since the "Group" column already provides the necessary value on which to transform. Not only does this simplify the query, but it also puts the values in the correct transformed column irregardless of the order or whether or not the "group" values are contiguous. (Also note that sqlite complains if group is used without delimiters since it is reserved keyword.)

SELECT User, 
       max(CASE WHEN "group" == 'A' THEN role END) as A,
       max(CASE WHEN "group" == 'B' THEN role END) as B,
       max(CASE WHEN "group" == 'C' THEN role END) as C
FROM SO52961250 t
GROUP BY User;

The excellent solution offered by @CPerkins has the potential drawback of losing information. For example, consider what would happen if the data for "Jack Brown" was presented in two rows:

Jack Brown |   A     |   admin
Jack Brown |   A     |   user

To ensure no information is lost, one could use GROUP_CONCAT instead of MAX:

SELECT User, 
       GROUP_CONCAT(CASE WHEN "group" == 'A' THEN role END) as A,
       GROUP_CONCAT(CASE WHEN "group" == 'B' THEN role END) as B,
       GROUP_CONCAT(CASE WHEN "group" == 'C' THEN role END) as C
FROM SO52961250 t
GROUP BY User;

You can use row_number() & do aggregation :

select User, 
       max(case when seq = 1 then role end) as a,
       max(case when seq = 2 then role end) as b,
       max(case when seq = 3 then role end) as c
from (select t.*,
             row_number() over (partition by User order by group) as seq
      from table t
     ) t
group by User;