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

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.


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.


great appendix! helped me to solve a similar problem with low effort and system load. I am using a Raspberry Pi to obtain 1wire-interface DS18B20 temperature sensor data as follows:

CREATE TABLE temps (Timestamp DATETIME, sensorID TEXT, temperature NUMERIC);

example:

sqlite> .headers on
sqlite> .mode column
sqlite> select * from temps where timestamp > '2014-02-24 22:00:00';

Timestamp            sensorID         temperature
-------------------  ---------------  -----------
2014-02-24 22:00:02  28-0000055f3f10  19.937
2014-02-24 22:00:03  28-0000055f0378  19.687
2014-02-24 22:00:04  28-0000055eb504  19.937
2014-02-24 22:00:05  28-0000055f92f2  19.937
2014-02-24 22:00:06  28-0000055eef29  19.812
2014-02-24 22:00:07  28-0000055f7619  19.625
2014-02-24 22:00:08  28-0000055edf01  19.687
2014-02-24 22:00:09  28-0000055effda  19.812
2014-02-24 22:00:09  28-0000055e5ef2  19.875
2014-02-24 22:00:10  28-0000055f1b83  19.812
2014-02-24 22:10:03  28-0000055f3f10  19.937
2014-02-24 22:10:04  28-0000055f0378  19.75
2014-02-24 22:10:04  28-0000055eb504  19.937
2014-02-24 22:10:05  28-0000055f92f2  19.937

using the SUBSTR() command I am "normalizing" the Timestamps to 10 minutes periods. With JOIN the sensorID is changed into a SensorName using the lookup-table 'sensors'

CREATE VIEW [TempsSlot10min] AS
SELECT SUBSTR(datetime(timestamp),1,15)||'0:00' AS TimeSlot,
SensorName,
temperature FROM
temps JOIN sensors USING (sensorID, sensorID);

example:

sqlite> select * from TempsSlot10min where timeslot >= '2014-02-24 22:00:00';

TimeSlot             SensorName  temperature
-------------------  ----------  -----------
2014-02-24 22:00:00  T1          19.937
2014-02-24 22:00:00  T2          19.687
2014-02-24 22:00:00  T3          19.937
2014-02-24 22:00:00  T4          19.937
2014-02-24 22:00:00  T5          19.812
2014-02-24 22:00:00  T6          19.625
2014-02-24 22:00:00  T10         19.687
2014-02-24 22:00:00  T9          19.812
2014-02-24 22:00:00  T8          19.875
2014-02-24 22:00:00  T7          19.812
2014-02-24 22:10:00  T1          19.937
2014-02-24 22:10:00  T2          19.75
2014-02-24 22:10:00  T3          19.937
2014-02-24 22:10:00  T4          19.937
2014-02-24 22:10:00  T5          19.875

now, the magic happens with the above mentioned CASE instruction.

CREATE VIEW [PivotTemps10min] AS
SELECT TimeSlot,
AVG(CASE WHEN sensorName = 'T1' THEN temperature END) AS T1,
AVG(CASE WHEN sensorName = 'T2' THEN temperature END) AS T2,
...
AVG(CASE WHEN sensorName = 'T10' THEN temperature END) AS T10
FROM TempsSlot10min
GROUP BY TimeSlot;

example:

select * from PivotTemps10min where timeslot >= '2014-02-24 22:00:00';

TimeSlot             T1          T2              T10
-------------------  ----------  ---------- ...  ----------
2014-02-24 22:00:00  19.937      19.687          19.687
2014-02-24 22:10:00  19.937      19.75           19.687
2014-02-24 22:20:00  19.937      19.75           19.687
2014-02-24 22:30:00  20.125      19.937          19.937
2014-02-24 22:40:00  20.187      20.0            19.937
2014-02-24 22:50:00  20.25       20.062          20.062
2014-02-24 23:00:00  20.25       20.062          20.062

The only problem remaining here is that the sensorName 'T1' ... 'T10' is now hardcoded into the VIEW [PivotTemps10min] and not taken from the lookup table.

Nonetheless, thank you very much for the answers in this thead!