SQLite long to wide formats?

Question

I wonder if there is a canonical way to convert data from long to wide format in SQLite (is that operation usually in the domain of relational databases?). I tried to follow this example for MySQL but I guess SQLite does not have the same IF construct... Thanks!

Answer 1

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');