[GENERAL] crosstab

[punnoose]

hi all
How could i use crostab to display variable number of columns. in the output 
There could be variable number of columns
Regards
Punnoose



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/crosstab-tp5722632.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pivot functions with variable number of columns

[punnoose]

I want to have a pivot like function in which i should have variable number
of columns.i went for crosstab but it doesnot support variable number of
columns.Can any body suggest an alternative.like if i have a event at a
particular time of the day like one at 02:35,11:34, then i should have
column name 02:35,11:34.
Please do help me.
Punnoose




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pivot-functions-with-variable-number-of-columns-tp5723013.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] origins/destinations

[denis punnoose]

I m new to PostgreSQL so please tell me the drawbacks of is this solution

Your Table 3 should not be a table it should be a array.


CREATE TABLE table3
(
    ori_des        int[][]
);



for origin area_n and destination area_m if count is k, then ori_des[n][m] = k.



--- On Tue, 19/5/09, Carson Farmer  wrote:

From: Carson Farmer 
Subject: [GENERAL] origins/destinations
To: pgsql-general@postgresql.org
Cc: "Carson Farmer" 
Date: Tuesday, 19 May, 2009, 10:27 PM

Hi list,

I have (what I thought was) a relatively simple problem, but my knowledge of 
sql is just not good enough to get this done:

I have a table which is basically a number of individuals with both their 
origin and destination as columns (see Table 1). In this case, origins and 
destinations are the census area in which they and work. What I would like to 
do is generate an nxn matrix (preferably output to csv but I'll take what I can 
get), where origins are on the y axis, and destinations on the x axis (see 
Table 3).

I can already group by both origins and destinations to produce Table 2, but I 
don't know what steps are needed to get to Table 3. Any help or suggestions are 
greatly appreciated!

Table 1

id   |   origin   |   destination
1        area1          area5
2        area1          area5
3        area1          area5
4        area2          area4
5        area4          area2
6        area5          area5
7        area2          area4
8        area2          area4
9        area4          area3
10       area3          area5


Table 2

id   |   origin   |   destination  |   count
1        area1          area5            3
4        area2          area4            3
5        area4          area2            1
6        area5          area5            1
9        area4          area3            1
10       area3          area5            1


Table 3

origins  |  area1  |  area2  |  area3  |  area4  |  area5  |  ...
area1        0         0         0         0         3
area2        0         0         0         3         0
area3        0         0         0         0         1
area4        0         1         1         0         0
area5        0         0         0         0         1



Regards,

Carson

-- Carson J. Q. Farmer
ISSP Doctoral Fellow
National Centre for Geocomputation (NCG),
Email: carson.far...@gmail.com
Web:   http://www.carsonfarmer.com/
      http://www.ftools.ca/



-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



  Get an email ID as yourn...@ymail.com or yourn...@rocketmail..com. Click 
here http://in.promos.yahoo.com/address