Re: How to create crosstab with 3 values in every crosstab column
| От | Misa Simic |
|---|---|
| Тема | Re: How to create crosstab with 3 values in every crosstab column |
| Дата | |
| Msg-id | CAH3i69=uxppyM9UXC_bW=XBMzn+RZHJEtA4Y3hFsEwGosSUb9A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: How to create crosstab with 3 values in every crosstab column ("Andrus" <kobruleht2@hot.ee>) |
| Ответы |
Re: How to create crosstab with 3 values in every crosstab column
|
| Список | pgsql-general |
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:
- loop trough distinict Accounts from Sales Table
- inside Accounts loop,
- WriteToFile(Account), then loop trough distinct Stores from Sales Table
- inside Stores Loop
- WriteToFile(';' + Store)
- WriteToFile(';' + calcSales(acount, Store)
- WriteToFile(';' + getBudget(acount, Store)
- WriteToFile(';' + calcPercent(acount, Store)
- after Stores loop make new line in file
- 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@hot.ee>
Thank you.My goal is to open result En excel.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...
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
Andrus.
В списке pgsql-general по дате отправления:
Следующее
От: "Andrus"Дата:
Сообщение: Re: How to create crosstab with 3 values in every crosstab column