How to pivot in SQLite or i.e. select in wide format a table stored in long format?

Question

I'd like to get a table which stores students data in long format and the marks they receive for all of their subjects in one query.

This is my table structure:

Table: markdetails

## studid ## ## subjectid ##  ## marks ##
     A1            3                50
     A1            4                60
     A1            5                70
     B1            3                60
     B1            4                80
     C1            5                95

Table: student info

Actual Structure:

## studid ##  ## name ##
      A1          Raam
      B1          Vivek
      c1          Alex

I want the result set to have the following wide format structure as result of the pivotization:

Table: Student Info

## studid ## ## name## ## subjectid_3 ## ## subjectid_4 ## ## subjectid_5 ##
      A1        Raam        50                60                 70
      B1        Vivek       60                80                null
      c1        Alex       null              null                95

How can I accomplish this in SQLite?

Answer 1

Since the author was not kind enough to give the SQL to create the schema, here it is for anyone who wants to try the solution from @Eric.

create table markdetails (studid, subjectid, marks);
create table student_info (studid, name);

insert into markdetails values('A1', 3, 50);
insert into markdetails values('A1', 4, 60);
insert into markdetails values('A1', 5, 70);
insert into markdetails values('B1', 3, 60);
insert into markdetails values('B1', 4, 80);
insert into markdetails values('C1', 5, 95);

insert into student_info values('A1', 'Raam');
insert into student_info values('B1', 'Vivek');
insert into student_info values('C1', 'Alex');

Here is an alternative solution using case with group by.

select
    si.studid,
    si.name,
    sum(case when md.subjectid = 3 then md.marks end) subjectid_3,
    sum(case when md.subjectid = 4 then md.marks end) subjectid_4,
    sum(case when md.subjectid = 5 then md.marks end) subjectid_5
from student_info si
join markdetails md on
        md.studid = si.studid
group by si.studid, si.name
;

For comparison, here is the same select statement from @Eric's solution:

select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_info u
    left outer join markdetails s3 on
        u.stuid = s3.stuid
        and s3.subjectid = 3
    left outer join markdetails s4 on
        u.stuid = s4.stuid
        and s4.subjectid = 4
    left outer join markdetails s5 on
        u.stuid = s5.stuid
        and s5.subjectid = 5
;

It will be interesting to see which one would perform better when there is a lot of data.