SQLite long to wide formats?

I wonder if there is a canonical way to conve开发者_C百科rt 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!

---

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

---

As an update to the excellent answer above (being a generalizable solution), and referencing the example cited in the OP, SQLite introduced the IIF() function in version 3.32.0:

iif(X,Y,Z)

The iif(X,Y,Z) function returns the value Y if X is true, and Z otherwise. The iif(X,Y,Z) function is logically equivalent to and generates the same bytecode as the CASE expression CASE WHEN X THEN Y ELSE Z END.

Using the accepted answer's DDL, the following query:

SELECT Country,
    MAX( IIF(key='President', Value, NULL)) President,
    MAX( IIF(key='Currency', Value, NULL)) Currency
FROM Long
GROUP BY Country;

... should reshape the long format:

┌────┬─────────┬───────────┬────────┐
│ ID │ Country │    Key    │ Value  │
├────┼─────────┼───────────┼────────┤
│ 1  │ USA     │ President │ Obama  │
│ 2  │ USA     │ Currency  │ Dollar │
│ 3  │ China   │ President │ Hu     │
│ 4  │ China   │ Currency  │ Yuan   │
└────┴─────────┴───────────┴────────┘

... to the wide format:

┌─────────┬───────────┬──────────┐
│ Country │ President │ Currency │
├─────────┼───────────┼──────────┤
│ China   │ Hu        │ Yuan     │
│ USA     │ Obama     │ Dollar   │
└─────────┴───────────┴──────────┘

Here we have transposed rows and columns.