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

SQLite Database Pivot: Convert long format data to wide format data

In database management, pivoting is a method of converting data from long format (where each row represents a single observation with multiple attributes) to wide format (where each row represents a different attribute and the columns represent the values ​​of those attributes) ) technology. This procedure is particularly useful when working with tables that store data in long format (as shown below):

<code>## studid ## ## subjectid ##  ## marks ##
A1            3                50
A1            4                60
A1            5                70
B1            3                60
B1            4                80
C1            5                95</code>

Question:

The goal is to convert the table above into a wide format where each student has a row representing their name and score for each subject. The desired output should look like this:

<code>## studid ## ## name## ## subjectid_3 ## ## subjectid_4 ## ## subjectid_5 ##
A1        Raam        50                60                 70
B1        Vivek       60                80                NULL
C1        Alex       NULL              NULL                95</code>

Solution using CASE statement and GROUP BY clause:

One way to achieve this in SQLite is to use a CASE statement in combination with a GROUP BY clause:

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;

Solution using left outer join:

Another approach involves using a left outer join to combine student information and scores for each subject:

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;

Both SQL queries will generate the required wide format tables, making it easier to analyze and visualize the data.

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