Hi Steve! - ( oh, and also Brona who replied to my query ) -
I found quite a good definition of a crosstab query on part of Microsoft's website -
"Crosstab query" - A query that caclulates a sum, average count or other type of total on records, and then groups the result by two types of information: One down the left hand side of the datasheet, and the other across the top".
In other words, you get a result-set that looks like Excel's "pivot tables" , going from
this ...
Name, Year, Value ----------------------- 'Simon', 2004, 32 'Russel', 2004, 64 'Simon', 2005, 128 'Russel', 2005, 32
.... to this ... 'Name', 2004, 2005
------------------------
'Simon', 32, 128
'Russel', 64, 32
So, you're basically "transposing" one (or more) of the columns.
( The above was taken from one of the "Python Cookbook" URLs at the end of this email. )
I'm pretty sure that MS Access can do this, but was wondering if SQLite can as well. ( I'm just poking around with it - it's not urgently needed for a "critical project" or anything ... :-) )
Here's some SQL (again, from MS I believe ) that shows what I mean . The *crucial* part is the "Pivot" statement - that's what makes it a crosstab query. ( I believe that the "pivot" statement is not standard SQL, no it's no surprise to see MS using it ;-)) )
********** Start of SQL code ******************
PARAMETERS [StartDate] Date;
TRANSFORM Sum([Subtotal]+[Tax]) AS Total
SELECT API.CategoryID
FROM [Account Payable] As AP INNER JOIN qryAccountPayableItem AS API ON AP.AccountPayableID = API.AccountPayableID
WHERE AP.AuthorisedDate BETWEEN [StartDate] And DateAdd("m", 12,
[StartDate])GROUP BY API.CategoryID
PIVOT Month([AuthorisedDate]) In (1,2,3,4,5,6,7,8,9,10,11,12)
WITH OWNERACCESS OPTION;
************** End of SQL code ***************************************
I just thought I'd pop in a couple of "bonuses" here -
Here is a *beautiful* example of a crosstab using Python - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/334695
.... and a related one using Python to group and summarise data - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/304162
Bye for now - - Andy
****************************************************************************************
Steve Frierdich wrote:
Andy What exactly is a crosstab query? Thanks Steve
Andy Elvey wrote:
Hi everyone -
I'm a "first-timer" here, and was wondering - does SQLite support crosstab queries? Many thanks in advance - bye for now!
- Andy
