Required help in building a crosstab query - PostgreSQL

Question

I have two tables.

Table1:

Label   Date    CT
A   2014-01-01  19
A   2014-02-01  10
A   2014-03-01  19
A   2014-04-01  18
B   2014-01-01  20
B   2014-02-01  16
B   2014-03-01  14
B   2014-04-01  16
C   2014-01-01  13
C   2014-02-01  12
C   2014-03-01  19
C   2014-04-01  14

Table2 :

Label   Date    CT
D   2014-01-01  19
D   2014-02-01  10
D   2014-03-01  19
D   2014-04-01  18
E   2014-01-01  20
E   2014-02-01  16
E   2014-03-01  14
E   2014-04-01  16
F   2014-01-01  13
F   2014-02-01  12
F   2014-03-01  19
F   2014-04-01  14

Desired Output :

Label   Jan'14  Feb'14  Mar'14  Apr'14  Total
A   19  10  19  18  66
B   20  16  14  16  66
C   13  12  19  14  58
D   19  10  19  18  66
E   20  16  14  16  66
F   13  12  19  14  58

I'm new to PostgreSQL.

I wanted to take the unique values of Label column from both the table.

And produce the sum total of count to their respective label.

I can combine both the tables in a straight forward method using UNION ALL.

But that'll not give me the desired output or the view like a pivot.

I did google on this but nothing could help me out.

Came across this in SO. And I'm still trying on with it.

But I actually don't have a clue whether it can be done or not.

Can someone help me in getting the desired output.

Thanks in advance!!

Sorry. By the time I got your answer. I found it myself. Thanks for your reply. — Unknown User, Apr 10 '14 at 10:47

score 0 · Accepted Answer · answered Apr 10 '14 at 07:24

Try Like This

select *,("Jan ''14" + "Feb ''14" + "Mar ''14" +"Apr ''14") as total 
from crosstab($$ 

select id,to_char(da,'Mon ''yy') as tt,no from t2

union all 

select id,to_char(da,'Mon ''yy') as tt,no from "T1"
 $$,$$values ('Jan ''14'), ('Feb ''14'),('Mar ''14'),('Apr ''14') $$) as at
(id text, "Jan ''14" integer,"Feb ''14" integer,"Mar ''14" integer,
"Apr ''14" integer) order by id

Required help in building a crosstab query - PostgreSQL

1 Answers1