Sign in to follow this  
Followers 0
sigil

trying to build a crosstab query in SQLite

1 post in this topic

I am having difficulty using the _SQLite functions. I have a table, let's say it's called Inventory and its fields are Product and Store, like this:

Product+Store

---------|---------

Pen | Bookstore

Soda | Drugstore

Journal | Bookstore

Pen | Supermarket

Paper | Drugstore

Paper | Bookstore

etc., and I want to produce a query that shows, for each object, how many of them there are in each store.

I know I can make a bunch of Case statements, like so:

Select Product,
                   sum(case store when bookstore then 1 else 0) as Bookstore,
                   sum(case store when drugstore then 1 else 0) as Drugstore,
                   sum(case store when supermarket then 1 else 0) as Supermarket
          From Inventory
          Group By Product;

but this isn't good because if I ever add another type of store then I have to change the code.

If I was using Access I could use Transform and Pivot to build the query how I want it, but these don't seem to be available in SQLite, as using them produces syntax errors.

I know this is more of an SQLite question than an Autoit question, but I haven't been able to find an SQLite forum on the web, and since I'm new to SQL as a whole, I am a bit bewildered.

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0