How to Pivot Long Format Data to Wide Format in SQLite Using JOINs or CASE Statements?

SQLite Pivot: Convert long format to wide format

In SQLite, pivoting refers to converting tables stored in long format into wide format for easier data analysis and processing. This is especially useful when working with data that has multiple measurements or attributes per row, as it allows us to visualize the data in a more structured and readable way.

To pivot data in SQLite, you can use the JOIN operation combined with conditional expressions, or use the CASE statement with GROUP BY. Here is a detailed guide to completing fluoroscopy using both techniques:

Use JOIN and conditional expressions

This method involves using JOIN and conditional expressions with the SUM() function to summarize the data for each desired column.

<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>

Use CASE statement and GROUP BY

Another approach is to use a CASE statement in a GROUP BY clause. This technique groups rows by necessary columns and calculates aggregate values ​​based on different scenarios.

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

Both methods can be used to pivot data in SQLite to give you the wide format you require.

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