Jump to content
Sign in to follow this  

trying to build a crosstab query in SQLite

Recommended Posts


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:



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  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.