How to create a crosstab view in SQLITE via Android?

Question

How to create a crosstab view in SQLITE via Android?

Navigation

#1 by (2 votes)

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"  │
                  └──────────────┘

You need to get the following:

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

Important Note: This is not join . I would like a crosstab , feature known in other banks, such as PostgreSQL.

android sqlite

asked by anonymous 22.02.2014 / 06:41

1 answer

Problem with multi editor in Wordpress How do I update backgroundWorker.ReportProgress () through a class in another project?

score 2 · Answer 1

Look, from what I've seen so far of SQLite there is no way to work with crosstab , or something like that, since a remarkable feature of the bank manager is its simplicity, which made it a database lightweight, making Android support native to it. The only negative side of SQLite is that it has been removed and / or implemented a lot of cool features that it has in other database managers. In the case of crosstab , there are very few database managers that have this tool.

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