title: Crosstab using PostgreSQL
body: <p>I have table <code>table1</code> which consist of following details.</p>
  
  <p><strong>Example</strong>:</p>
  
  <pre><code>create table table1
  ( 
     slno varchar(10),
     joiningdate date,
     joiningtime time
  ); 
  </code></pre>
  
  <p>Inserting some rows:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>Now I need to display it into following format:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For which I have tried the following script:</p>
  
  <pre><code> 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);
  </code></pre>
  
  <p>But got stuck how to count <code>tc</code>(joiningtime hours) and add it to appropriate column.</p>