How to Pivot Student Marks Data from Long to Wide Format in SQLite?

Pivot table structure in SQLite

Introduction:

Data flattening, also known as long format, is a common method of storing data in SQLite. However, sometimes it is necessary to convert this data into a wide format, where each row represents a subject and the columns represent the scores of that subject for a specific student. This process is called perspective.

Problem statement:

You have two tables, one that stores student data (studid, name) and the other that stores their scores in different subjects (studid, subjectid, marks). The task is to retrieve student information in a wide format where each row represents a student and the columns represent their marks in a specific subject.

Solution using CASE and GROUP BY:

<code class="language-sql">SELECT
    si.studid,
    si.name,
    SUM(CASE WHEN md.subjectid = 3 THEN md.marks END) AS subjectid_3,
    SUM(CASE WHEN md.subjectid = 4 THEN md.marks END) AS subjectid_4,
    SUM(CASE WHEN md.subjectid = 5 THEN md.marks END) AS subjectid_5
FROM
    student_info si
JOIN
    markdetails md ON md.studid = si.studid
GROUP BY
    si.studid, si.name;</code>

This solution uses the CASE statement in the SUM() aggregate function. For each subjectid (3, 4, 5), it checks if the markdetails table contains a matching row and sums the corresponding scores. The GROUP BY clause ensures that the results are grouped by student ID and name.

Alternative solution using left outer join:

<code class="language-sql">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;</code>

This alternative solution uses a LEFT OUTER JOIN to match students with their respective scores in the markdetails table. For students who do not have scores in a specific subject, the score columns (subjectid_3, subjectid_4, subjectid_5) will be returned as NULL.

The above is the detailed content of How to Pivot Student Marks Data from Long to Wide Format in SQLite?. For more information, please follow other related articles on the PHP Chinese website!