How to Pivot in Sqlite or I.E. Select in Wide Format a Table Stored in Long Format

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

First you need to change the current table to a temp table:

alter table student_info rename to student_name

Then, you'll want to recreate student_info:

create table student_info add column (
    stuid VARCHAR(5) PRIMARY KEY,
    name VARCHAR(255),
    subjectid_3 INTEGER,
    subjectid_4 INTEGER,
    subjectid_5 INTEGER
)

Then, populate student_info:

insert into student_info
select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_temp 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

Now, just drop your temp table:

drop table student_temp

And that's how you can quickly update your table.

SQLite lacks a pivot function, so the best you can do is hard-code some left joins. A left join will bring match any rows in its join conditions and return null for any rows from the first, or left, table that don't meet the join conditions for the second table.

sql: long to wide format without using PIVOT

If I understand your question correctly, you can do conditional aggregation:

select
    user_id,
    time_id,
    max(case when val0 = 1 then score end) score0,
    max(case when val1 = 1 then score end) score1,
    max(case when val2 = 1 then score end) score2
from mytable
group by user_id, time_id

Maybe you want to use actual_value to pivot instead of the val[n] columns:

select
    user_id,
    time_id,
    max(case when actual_value = 0 then score end) score0,
    max(case when actual_value = 1 then score end) score1,
    max(case when actual_value = 2 then score end) score2
from mytable
group by user_id, time_id

SQLite pivot producing alternate NULLS

The trick when you are using CASE/WHEN is to use aggregative functions like MAX and then group by all the non-aggragate columns :

SELECT 
   band,
   Month,
   MAX(CASE 
          when status = 'one' then response_avg
      END) as One,
   MAX(CASE 
          when status = 'two' then response_avg
      END) as Two
FROM t1
GROUP BY band,
   Month

How to transpose a table in SQLite?

You can use row_number() & do aggregation :

select User, 
       max(case when seq = 1 then role end) as a,
       max(case when seq = 2 then role end) as b,
       max(case when seq = 3 then role end) as c
from (select t.*,
             row_number() over (partition by User order by group) as seq
      from table t
     ) t
group by User;

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

First you need to change the current table to a temp table:

alter table student_info rename to student_name

Then, you'll want to recreate student_info:

create table student_info add column (
    stuid VARCHAR(5) PRIMARY KEY,
    name VARCHAR(255),
    subjectid_3 INTEGER,
    subjectid_4 INTEGER,
    subjectid_5 INTEGER
)

Then, populate student_info:

insert into student_info
select
    u.stuid,
    u.name,
    s3.marks as subjectid_3,
    s4.marks as subjectid_4,
    s5.marks as subjectid_5
from
    student_temp 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

Now, just drop your temp table:

drop table student_temp

And that's how you can quickly update your table.

SQLite lacks a pivot function, so the best you can do is hard-code some left joins. A left join will bring match any rows in its join conditions and return null for any rows from the first, or left, table that don't meet the join conditions for the second table.

Pivot in SQLite with missing records

This is conditional aggregation with a left join:

select s.studid,
       max(case when m.subjid = 3 then mark else 0 end) as subjid_3,
       max(case when m.subjid = 4 then mark else 0 end) as subjid_4,
       max(case when m.subjid = 5 then mark else 0 end) as subjid_5
from students s left join
     marks m
     on s.studid = m.studid
group by s.studid;

How can I get MAX() field in a sqlite table using additional condition?

In SQLite you can do it with this non-standard SQL query:

SELECT Id, Name, MAX(Date) AS Date
FROM MyTable
GROUP BY Name

This works in SQLite because a column like Id which does not appear in the group by clause and also it is not aggregated (bare column) is returned from the row that contains the max Date.

See the demo.

---

Related Topics