How to create a view with Crosstab in SQLITE via Android?

Asked

Viewed 156 times

4

I have the following tables: 

┌──────────────┐  ┌──────────────┐   ┌─────────────────────────────┐
│ TABLE pessoa |  | TABLE lente  |   | TABLE pessoa_lente          |
├──────────────┤  ├──────────────┤   ├─────────────────────────────┤
│ _id, NAME    │  │ _id, COLOR   │   │ _id, idpessoa, idlente, qty │ 
├──────────────┤  ├──────────────┤   ├─────────────────────────────┤
│ 1, "mary"    │  │ 1, "BLACK"   │   │ 1, 1, 1, 50                 │
├──────────────┤  ├──────────────┤   ├─────────────────────────────┤
│ 2, "juan"    │  │ 2, "BLUE"    │   │ 2, 1, 3, 30                 │
├──────────────┤  ├──────────────┤   ├─────────────────────────────┤
│ 3, "jose"    │  │ 3, "GRAY"    │   │ 3, 1, 4, 25                 │
├──────────────┤  ├──────────────┤   ├─────────────────────────────┤
│ ...          │  │ 4, "YELLOW"  │   │ ...                         │
└──────────────┘  ├──────────────┤   └─────────────────────────────┘
                  │ 4, "YELLOW"  │
                  └──────────────┘

And I need to get the following:

[crosstab]
NAME   | BLACK | GRAY | YELLOW
"mary" | 50    | 30   | 25
...

Important Note: It’s not about join. I would like a crosstab, resource known in other banks such as Postgresql.

android sqlite

1 answer

2

Look, from what I’ve seen so far SQLite there is no way to work with the crosstab, or something similar, because a striking feature of the database manager is its simplicity, which made it a lightweight database, causing Android to give native support to it. The only downside of SQLite is that has been removed and/or not implemented a lot of legal resources that has in other database managers. Already in the case of crosstab are very few database managers that have this tool.

In my view you will have to treat this type of data by the app and not by the bank.

  • To be quite honest, as I work with robust database managers like Postgresql, with Sqlite I feel like a confectioner with only sugar as a raw material to produce the full range of possible flavors. The Sqlite NO there are a lot of things that make me feel safe using databases, rather than creating flat text like data set . Recently, for example, I discovered the case of the Boolean type (not native?!) and every day I have more surprises. I don’t think that things essential to a bank should be discarded in preference to lightness.

Browser other questions tagged android sqlite

You are not signed in. Login or sign up in order to post.