creating a pivot in postgres

Question

I am using postgres RDBMS and want to pivot on this.

How do i pivot the results in postgres?

when I exe the below query I get this result

enter image description here

I tried this before posting!

select * from 
(
select count(tnx_id) as x,
case act_dt between 20140301 and 20140831 then '0-6 m'
when act_dt between 20130901 and 20140231 then '7-12 m'
when act_dt between 20130301 and 20130831 then '13-18 m'
else '18+'
end as act_bucket,

case when tnx_dt_int between 20140301 and 20140831 then '0-6 m'
when  tnx_dt_int between 20130901 and 20140231 then '7-12 m'
when tnx_dt_int between 20130301 and 20130831 then '13-18 m'
else '18+'
end as tnx_bucket



 from card 
inner join tnx on card_id=tnx_cardh_id
group by act_bucket,tnx_bucket)
x1
pivot(x)
(
for tnx_bucket in([0-6 m],[7-12 m],[13-18 m],[18+])
)



so that i get like this 

              act_bucket
tnx_bucket  0-6 m 7-12 m 13-18 m 18+
0-6m         <  here filled with Count(tnx_id)      >
7-12 m
13-18 m
18+

jmvivo · Accepted Answer · 2014-09-19 10:13:38Z

2

Your are looking for crosstab functions of tablefunc postgreSQL module

This is an example of it (from doc):

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

Good luck!

answered Sep 19, 2014 at 10:13

jmvivo

2,6731 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jmvivo Over a year ago

You're welcome. Please, If this is the right answer click on the check mark!

Collectives™ on Stack Overflow

creating a pivot in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related