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, Aug 11 '14 at 14:55
Why split `joiningdate` and `joiningtime`? That's what the `timestamp` datatype is for. — Craig Ringer, Aug 12 '14 at 03:33

score 2 · Accepted Answer · answered Aug 12 '14 at 03:51

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. — Sarfaraz Makandar, Aug 12 '14 at 04:34
Can you please help me for this:http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records — Sarfaraz Makandar, Mar 05 '15 at 06:23

score 1 · Answer 2 · answered Aug 12 '14 at 01:17

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

Crosstab using PostgreSQL

2 Answers2