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

by

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
;

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. Oracle SQL pivot query
  3. SQLite DateTime comparison
  4. How can I combine multiple rows into a comma-delimited list in Oracle? [duplicate]
  5. SQL transpose full table
  6. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  7. Pivot on Multiple Columns using Tablefunc
  8. Dynamically create columns sql
  9. Deleting duplicate rows from sqlite database
  10. What does the SQL Standard say about usage of backtick(`)?
  11. TSQL PIVOT MULTIPLE COLUMNS
  12. Pivot rows to columns without aggregate
  13. Declare variable in SQLite and use it
  14. How to get a list of column names on Sqlite3 database?
  15. How to pivot rows into columns (custom pivoting)
  16. Using pivot on multiple columns of an Oracle row
  17. PIVOT query on Distinct records
  18. GROUP_CONCAT in SQLite
  19. Match only entire words with LIKE?
  20. What is causing Foreign Key Mismatch error?
  21. SQL Transpose Rows as Columns
  22. Pivoting in DB2
  23. Android multiple databases open
  24. How to split comma-separated values?
  25. sqlite select with condition on date
  26. How to compare sqlite TIMESTAMP values
  27. SQLite Order By Date1530019888000
  28. SQL – Inserting a row and returning primary key
  29. IF() statement alternative in SQLite
  30. Copy table structure to new table in sqlite3

Leave a Comment