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

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?

5 Answers

Here is the SQL to create the schema for this example. 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
;

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!

Thanks to @pospec4444's link here is modified version of @haridsv's awesome answer. It uses filter clause to be little more concise

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

If you have a simpler requirement of bundling together the children in the same field, group_concat is your friend.

Huge thanks to Simon Slaver from this thread:

Pop Glow

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

5 Answers

Your Answer

Sign up or log in

Post as a guest

You Might Also Like

Minecraft Not Enough Items problem

Do these new maps exist in GTA V for PS3?

Master Quest - Does it reinforce the temple order?

How can I find the cars I buy on my character's cell phone?