Re: pivot functions with variable number of columns

On Thu, Sep 6, 2012 at 10:44 AM, punnoose <punnoose(dot)pj(at)dwisesolutions(dot)com>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/