How to Pivot Long to Wide Format Data in SQLite Using CASE and GROUP BY?

Convert tabular data from long to wide format using SQLite

In data analysis, it is often necessary to convert data from long format (each row represents one record and multiple columns represent different values) to wide format (each record occupies multiple columns). This operation is called pivoting.

We have a table called markdetails with the following structure:

• studid subjectid marks

  A1 3 50
  A1 4 60
  A1 5 70
  B1 3 60
  B1 4 80
  C1 5 95

There is also a table named student_info with the following structure:

• studid name

  <code>  A1          Raam
    B1          Vivek
    c1          Alex</code>

We want to pivot this data into the following wide format:

• studid name subjectid_3 subjectid_4 subjectid_5

  <code>  A1        Raam        50                60                 70
    B1        Vivek       60                80                NULL
    c1        Alex       NULL              NULL                95</code>

One way to do this is to use the CASE statement with GROUP BY. The following query will return the required wide format:

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

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