Sqlite Long to Wide Formats

SQLite long to wide formats?

IF is a non-standard MySQL extension. It's better to always use CASE which is standard SQL and works in all compliant databases, including SQLite and MySQL (and MSSQL, Oracle, Postgres, Access, Sybase... and on and on).

Here's an example of how to do the same query with CASE:

SELECT      Country,
            MAX(CASE WHEN Key = 'President' THEN Value ELSE NULL END) President,
            MAX(CASE WHEN Key = 'Currency' THEN Value ELSE NULL END) Currency
FROM        Long
GROUP BY    Country
ORDER BY    Country;

Here's another way to represent the same query using joins. I think this is probably more efficient, but it assumes there's only one record for each key value within each group (the CASE version does too, but will not result in extra rows if that's not true, just less-than-predictable results).

SELECT
    D.Country,
    P.Value President,
    C.Value Currency
FROM
    (
        SELECT DISTINCT Country
        FROM    Long
    ) D
            INNER JOIN
    (   SELECT  Country, Value
        FROM    Long
        WHERE   Key = 'President'
    ) P
            ON
        D.Country = P.Country
            INNER JOIN
    (   SELECT  Country, Value
        FROM    Long
        WHERE   Key = 'Currency'
    ) C
            ON
        D.Country = C.Country
ORDER BY
    D.Country;

And for the record, here's the DDL and test data I was using:

CREATE TABLE Long (ID INTEGER PRIMARY KEY AUTOINCREMENT, Country TEXT, Key TEXT, Value TEXT);

INSERT INTO Long VALUES (NULL, 'USA', 'President', 'Obama');
INSERT INTO Long VALUES (NULL, 'USA', 'Currency', 'Dollar');
INSERT INTO Long VALUES (NULL, 'China', 'President', 'Hu');
INSERT INTO Long VALUES (NULL, 'China', 'Currency', 'Yuan');

sqlite: wide v. long performance

The "long" format is the preferred way to go here, for so many reasons. First, if you use the "wide" format and there is ever a need to add more series, then you would have to add new columns to the database table. While this is not too much of a hassle, in general once you put a schema into production, you want to avoid further schema changes.

Second, the "long" format makes reporting and querying much easier. For example, suppose you wanted to get a count of rows/data points for each series. Then you would only need something like:

SELECT series, COUNT(*) AS cnt
FROM yourTable
GROUP BY series;

To get this report with the "wide" format, you would need a lot more code, and it would be as verbose as your sample data above.

The thing to keep in mind here is that SQL databases are built to operate on sets of records (read: across rows). They can also process things column wise, but they are not generally setup to do this.

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.

Mysql, reshape data from long / tall to wide

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

Something like:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

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

Reshape data long to wide in MySQL

I would suggest that you combine them into a single comma-separated string:

select category,
       group_concat(zip_code order by zipnum) as zip_codes
from t
group by category;

Because your string will be so long, you will need to set group_concat_max_len to a value larger than its default value.

Why don't you want these as columns? Well, MySQL has a hard limit of 4,096 columns in a table and I think this limit applies to result sets as well. The limit, though, is further complicated by the storage engine and other settings.. There is enough complication that I just wouldn't want to dynamically be creating queries that could result in thousands of columns.

You might find that a JSON structure is more convenient than a "mere" string. MySQL also supports that.

Convert wide format data to long format using SQL

You can use a lateral join:

select t.id, v.*
from t cross join lateral
     (values ('a', pg_a), ('b', pg_b)
     ) v(pg, value);

If you are new to SQL, you might you might be more comfortable with union all:

select id, 'a' as pg, pg_a as value
from t
union all
select id, 'b', pg_b
from t;

---

Related Topics