Re: How to create crosstab with 3 values in every crosstab column

Hi,

I think you dont understand me (or I do not understand you :) )

the point is - it is not possible to get unknown no of columns in 1 SQL
query...

i.e.

Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00

to get:

Acount, St1 , St2
100, 1000.00 2000.00

to get that in your query... St1 and St2 - must be hardcoded... (is there 1
column per Store, or 3 columns per store it is less important...)

if it St1 and St2 are hardcoded in query, even if in table is:

Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00

We would get the same result...actually if we want St3 we need to change
our query and add St3 in it...

that is the reason why we use Dynamic SQL to build the query...

when you build your Dynamic SQL query... you could use COPY (dynamicQuery)
TO CSV file...

or instead of to build dynamic query, you can export directly to file...

So still we need our functions: CalcSales(acount, Store),
getBudget(account, Store): calcPercent(account, Store)

make export function in some procedural language you are familiar with...
(plpgql, python, perl... whatever - just pure SQL is not possible...)

wich will:

1. loop trough distinict Accounts from Sales Table
2. inside Accounts loop,
3. WriteToFile(Account), then loop trough distinct Stores from Sales
Table
4. inside Stores Loop
5. WriteToFile(';' + Store)
6. WriteToFile(';' + calcSales(acount, Store)
7. WriteToFile(';' + getBudget(acount, Store)
8. WriteToFile(';' + calcPercent(acount, Store)
9. after Stores loop make new line in file
10. and after Accounts loop close the file..

Optionally you can first loop trough Stores loop to create header line...

Kind Regards,

Misa

that is the reason why we use dynamic query...

2012/2/6 Andrus <kobruleht2(at)hot(dot)ee>

> Thank you.
>
>
> first calculate how much columns we should return...No of stores in in
>> your
>> Sales table (multiply 3 in your case)...
>> (and based on that - build the grid in UI)
>> then with knowing that - we select full "table" with simple 3 functions:
>> CalcSales(coount, Store), getBudget(account, Store)
>> and then build dynamic query with those 3 functions many times as we have
>> stores as columns...
>>
>
> My goal is to open result En excel.
> Instead of building dynamic query isn't it reasonable to create csv file
> directly from code without using crosstab
> but creating it manually ?
> It looks that crosstab does not have any advantages instead on manual
> crosstab creation ?
>
> Another possibility is to merge those 3 columns into single column and
> crosstab it .
> After that create csv file by splitting single column into 3 columns.
>
> Can some postgres array function or something like used to split 1 column
> into 3 columns in result without
> building query string manually ?
>
> I posted this also in
>
> http://stackoverflow.com/**questions/9162497/how-to-**
> generate-crosstab-with-3-**columns-for-every-store-in-**postgresql<http://stackoverflow.com/questions/9162497/how-to-generate-crosstab-with-3-columns-for-every-store-in-postgresql>
>
> Andrus.
>