Sqlite Transpose row to column example


This is a neat example of how to transpose grouped data from rows into columns.
That is, rows that have a value in common in a particular column can have the values in that group placed on the same row.

The sort of thing this could be used for is to allow for rows to hold a persons name parts (ie. first_name, middle_name, last_name).

The query is based on my ranked row example which gave 1st, 2nd, 3rd etc values for groups of data.

Full code which transposes the rows

select
  t4.f1,
    coalesce(max(case t4.fy when 1 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 2 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 3 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 4 then t4.f2 END), ' ') as q4
from (
  select
    t1.f1,
    t1.f2,
    (
      select
        t1.rowid - t3.rowid + 1
      from
        (
          select
            distinct f1, f2
          from
            tbl1
          order by f1
        ) t3
      where
        t3.f1 = t1.f1
    ) fy
  from
    (select distinct f1, f2 from tbl1) t1
  order by
    t1.f1,
    t1.f2,
    fy
) t4
group by
  t4.f1
order by t4.f1;

Original query output

f1|f2|fy
a|blue|1
a|yellow|2
b|green|1
c|black|1
c|false|2
c|true|3
d|sdsdsd|1
e|orange|1

Transposed Output

f1|q4
a|blue yellow
b|green
c|black false true
d|sdsdsd
e|orange

The outer select does the actual transposition

select
  t4.f1,
    coalesce(max(case t4.fy when 1 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 2 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 3 then t4.f2 END), ' ') || ' ' ||
    coalesce(max(case t4.fy when 4 then t4.f2 END), ' ') as q4
from (
...

The rank is stored in column t4.fy. There must be a select case hand coded for the maximum number of rows that are in any particular group, but the coalesce will remove any nulls from the end if a group doesn’t have the full number of rows. In this case the rows values are concatenated together but could also have been left as separate columns.

Conclusion:

The sort of thing this could be used for is to allow for rows to hold a persons name parts (ie. first_name, middle_name, last_name). The name parts could all be joined together on a single row, with group column being the person_id.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s