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

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!

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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related