CODEWITHSUNDEEP
postgresqlpostgresql-9.3crosstab
MAK
MAK

Reputation: 7260

PostgreSQL 9.3: Crosstab query

I have a table called as testing which is having four columns as shown below.

create table testing
(
cola varchar(10),
colb varchar(10),
colc varchar(10),
cold varchar(10)
);

Insertion of some rows:

insert into testing values('A1','City1','X1','Z1'),
              ('A2','City2','X2','Z2'),
              ('A3','City3','X3','Z3'),
              ('A4','City4','X4','Z4');

Expected Result: I want to show the result which looks like as shown below:

cola  City1  City2  City3  City4  colc  cold
--------------------------------------------
A1      1      0      0      0     X1    Z1
A2      0      1      0      0     X2    Z2
A3      0      0      1      0     X3    Z3
A4      0      0      0      1     X4    Z4

My try:

I have tried with the following script but getting an error.

select * from crosstab
(
'select cola,colb,count(colb),colc,cold from testing group by 1,2,4,5 order by 1,2,4,5',
'select distinct colb from testing'
)
as t
(
    cola varchar(10),City1 integer,City2 integer,City3 integer,City4 integer,colc varchar(10),cold varchar(10)
);

Error Details: 

ERROR:  invalid input syntax for integer: "City1"

Upvotes: 1

Views: 397

Answers (1)

Kokizzu
Kokizzu

Reputation: 26828

Maybe this is what you want?

select x1.*, x2.colc, x2.cold from crosstab
( $$select x1.cola, x2.colb,
     CASE WHEN x1.colb = x2.colb THEN 1 ELSE 0 END
    FROM testing x1 CROSS JOIN testing x2 
  $$
) AS x1( cola VARCHAR(10), City1 INT, City2 INT, City3 INT, City4 INT)
LEFT JOIN testing x2 ON x1.cola = x2.cola;

Result:

 cola | city1 | city2 | city3 | city4 | colc | cold 
------+-------+-------+-------+-------+------+------
 A1   |     1 |     0 |     0 |     0 | X1   | Z1
 A2   |     0 |     1 |     0 |     0 | X2   | Z2
 A3   |     0 |     0 |     1 |     0 | X3   | Z3
 A4   |     0 |     0 |     0 |     1 | X4   | Z4

Upvotes: 1

Related Questions