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
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)
