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.

Answer 2

First you need to change the current table to a temp table:

alter table student_info rename to student_name 

Then, you'll want to recreate student_info:

create table student_info add column (     stuid VARCHAR(5) PRIMARY KEY,     name VARCHAR(255),     subjectid_3 INTEGER,     subjectid_4 INTEGER,     subjectid_5 INTEGER ) 

Then, populate student_info:

insert into student_info select     u.stuid,     u.name,     s3.marks as subjectid_3,     s4.marks as subjectid_4,     s5.marks as subjectid_5 from     student_temp 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 

Now, just drop your temp table:

drop table student_temp 

And that's how you can quickly update your table.

SQLite lacks a pivot function, so the best you can do is hard-code some left joins. A left join will bring match any rows in its join conditions and return null for any rows from the first, or left, table that don't meet the join conditions for the second table.