Crosstab using PostgreSQL

Question

I have table table1 which consist of following details.

Example:

create table table1 ( slno varchar(10), joiningdate date, joiningtime time );

Inserting some rows:

insert into table1 values('a1','09-08-2011','10:00:00'); insert into table1 values('a1','09-08-2011','10:00:00'); insert into table1 values('a2','19-08-2011','11:00:00'); insert into table1 values('a2','20-08-2011','12:00:00');

Now I need to display it into following format:

slno joiningdate 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 -------------------------------------------------------------------------------------------------------------------------------------- a1 09-08-2011 2 a2 19-08-2011 1 a2 20-08-2011 1

For which I have tried the following script:

 select * from crosstab(' select slno,joiningdate , to_char(joiningtime, ''HH24'') as tc, count(tc)::int from table1 group by 1,2,3,4 order by 1,2,3,4' ,$$VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'), ('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'), ('21'),('22'),('23')$$) as ct(slno varchar,joiningdate date,"01" int,"02" int,"03" int,"04" int,"05" int,"06" int,"07" int,"08" int,"09" int,"10" int, "11" int,"12" int,"13" int,"14" int,"15" int,"16" int,"17" int,"18" int,"19" int,"20" int, "21" int,"22" int, "23" int);

But got stuck how to count tc(joiningtime hours) and add it to appropriate column.

+1 example data. I'll see if I can come back to this when I'm awake tomorrow... — Craig Ringer
– Craig Ringer, Commented Aug 11, 2014 at 14:55
Why split joiningdate and joiningtime? That's what the timestamp datatype is for. — Craig Ringer
– Craig Ringer, Commented Aug 12, 2014 at 3:33

Craig Ringer · Accepted Answer · 2014-08-12 03:51:37Z

First, produce a series of rows with the hourly counts.

select slno, joiningdate, hour, sum(case when extract(hour from joiningtime) = hour then 1 end) from table1 cross join generate_series(0,23) h(hour) group by slno, joiningdate, hour;

then, because crosstab can't deal with multiple column row keys, consolodate the row key using a composite type:

CREATE TYPE ctrowid as ( slno text, joiningdate date ); select ROW(slno, joiningdate) :: ctrowid, hour, sum(case when extract(hour from joiningtime) = hour then 1 end) from table1 cross join generate_series(0,23) h(hour) group by slno, joiningdate, hour order by 1,2;

so the query produces tuples of (rowid, category, value) as required by crosstab. Then wrap it in a crosstab, e.g.

SELECT * FROM crosstab(' select ROW(slno, joiningdate)::ctrowid, hour::text, sum(case when extract(hour from joiningtime) = hour then 1 end)::integer from table1 cross join generate_series(0,23) h(hour) group by slno, joiningdate, hour order by 1, 2 ') ct(rowid ctrowid, h0 integer, h1 integer, h2 integer, h3 integer, h4 integer, h5 integer, h6 integer, h7 integer, h8 integer, h9 integer, h10 integer, h11 integer, h12 integer, h13 integer, h14 integer, h15 integer, h16 integer, h17 integer, h18 integer, h19 integer, h20 integer, h21 integer, h22 integer, h23 integer);

producing:

 rowid | h0 | h1 | h2 | h3 | h4 | h5 | h6 | h7 | h8 | h9 | h10 | h11 | h12 | h13 | h14 | h15 | h16 | h17 | h18 | h19 | h20 | h21 | h22 | h23 -----------------+----+----+----+----+----+----+----+----+----+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+----- (a1,2011-08-09) | | | | | | | | | | | 2 | | | | | | | | | | | | | (a2,2011-08-19) | | | | | | | | | | | | 1 | | | | | | | | | | | | (a2,2011-08-20) | | | | | | | | | | | | | 1 | | | | | | | | | | | (3 rows)

You can then unpack the rowid into separate fields in an outer query if you want.

Yes, the need to specify all the columns is ugly, and makes crosstab much less useful than it should be.

I think order by for hour in crosstab query makes shuffling in table.
Can you please help me for this:stackoverflow.com/questions/28855405/…

NullEverything · Accepted Answer · 2014-08-12 01:17:21Z

I'm sure there is a more efficient way of doing this, but this query should give you what you are looking for. I only provided a portion of your table, but I'm sure you can write out the rest.

SELECT DISTINCT ON(slno,joiningdate) slno,joiningdate, CASE WHEN joiningtime = '01:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "01", CASE WHEN joiningtime = '02:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "02", CASE WHEN joiningtime = '03:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "03", CASE WHEN joiningtime = '10:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "10", CASE WHEN joiningtime = '11:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "11", CASE WHEN joiningtime = '12:00:00' THEN count(*) OVER (PARTITION BY joiningtime) ELSE NULL END AS "12" FROM table1

Here's a screenshot of the result for the query

Collectives™ on Stack Overflow

Crosstab using PostgreSQL

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related