Search Postgresql Archives

Re: pivot functions with variable number of columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Thu, Sep 6, 2012 at 10:44 AM, punnoose <punnoose.pj@xxxxxxxxxxxxxxxxxx> wrote:
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


Am not sure how your table structure is designed to use the best of crosstab. Here is a simple example to give some light on how to work with crosstab it.

Table & Data:

CREATE TABLE pivot_test (id integer, customer_id integer, product_code VARCHAR, quantity integer);


INSERT INTO pivot_test VALUES (1, 1, 'A', 10);

INSERT INTO pivot_test VALUES (2, 1, 'B', 20);

INSERT INTO pivot_test VALUES (3, 1, 'C', 30);

INSERT INTO pivot_test VALUES (4, 2, 'A', 40);

INSERT INTO pivot_test VALUES (5, 2, 'C', 50);

INSERT INTO pivot_test VALUES (6, 3, 'A', 60);

INSERT INTO pivot_test VALUES (7, 3, 'B', 70);

INSERT INTO pivot_test VALUES (8, 3, 'C', 80);

INSERT INTO pivot_test VALUES (9, 3, 'D', 90);

INSERT INTO pivot_test VALUES (10, 4, 'A', 100);


postgres=# select * from pivot_test;

 id | customer_id | product_code | quantity 

----+-------------+--------------+----------

  1 |           1 | A            |       10

  2 |           1 | B            |       20

  3 |           1 | C            |       30

  4 |           2 | A            |       40

  5 |           2 | C            |       50

  6 |           3 | A            |       60

  7 |           3 | B            |       70

  8 |           3 | C            |       80

  9 |           3 | D            |       90

 10 |           4 | A            |      100

(10 rows)

Here is Pivot kind result:

postgres=select * from crosstab

       ('select customer_id::text,

                product_code::text,

                quantity::text

                from pivot_test

                where product_code=''A'' or product_code=''B'' or product_code=''C''

                order by 1,2'

       ) as ct(customer_id text, "A" text,"B" text,"C" text);


 customer_id |  A  | B  | C

-------------+-----+----+----

 1           | 10  | 20 | 30

 2           | 40  | 50 |

 3           | 60  | 70 | 80

 4           | 100 |    |

(4 rows)

Someone, might have better example. Timely you can work with above example. 

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux