TRANSFORM command // COM licensing

[Ruslan Zasukhin]

On 8/25/06 2:31 AM, "fellerd at cox.net" <fellerd at cox.net> wrote:

>> Well, if you give points on MS docs, specifications, algorithms of this
>> function(s) I think it will take quite small time to implement them in
>> engine...This is not a problem actually.

> I have no idea how MS does this under the hood. Apollo Database Server from
> Vista software has a transpose sql feature like MSAccess TRANSFORM command.

> It's basically a MSExcel pivot table. You group 1 or more fields, then
> instead of selecting a field to show vertically in the resultset this field
> is transformed horizontally across the resultset as field name headers based
> on the values in the database (In MSAccess you set this field up in the
> query as the Column Header). Lastly you select one or more fields which has
> the Value (Min, Max, Sum, Count, Dev ...) you want to compute.

---------------- 
> Here's a very common crosstab/pivot. You want to see 5 years of sales profit
> data for your customers.
> 
> Here's how we setup the query in MSAccess:
> 1) Customer field is the Group field.
> 2) Year( [Order Date) ) as your Column Header field
> 3) Profit field with Sum function is the Value field.
> 
> Here are the records in the database:
> Customer    Order Date    Profit
> ========    ==========    ======
> ABC  01/01/02          1
> DEF  01/01/02  2
> GHI  01/01/02  3
> ABC  01/01/02          1
> DEF  01/01/02  1
> GHI  01/01/02  1
> ABC  01/01/03          4
> DEF  01/01/03  5
> GHI  01/01/03  6
> ABC  01/01/04          1
> DEF  01/01/04  2
> GHI  01/01/04  3
> ABC  01/01/05          1
> DEF  01/01/05  2
> GHI  01/01/05  3
> ABC  01/01/06          1
> DEF  01/01/06  2
> GHI  01/01/06  3
> 
> Here's the result of the sql transform or pivot feature.
> Customer 2002 2003 2004 2005 2006 Total Profit
> ABC  2 4 1 1 1 9
> DEF  3 5 2 2 2 14
> GHI  4 6 3 3 3 19
> 
> MSAcces even has a setting to place the transformed year fields in any order
> you want. I could set the years to read in order form left to right as 2003,
> 2005, 20006, 2002, 2004.

> This the best I can do for docs on this pivot feature.

Okay I see. This sounds like 1-3 days of work for developer.

And this can be done via SQL, right?
Can you show syntax or give quick URL on this.

Even better, please add such Feature Request into our Mantis.
Just copy this your letter there.
And if possible provide URLs to online docs.
 

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]