MAK MAK - 1 month ago 8
SQL Question

Pivot table for data per hour

I want to display a pivot table for the given below records:

I have a table called

fooo
with the two columns:

create table fooo (
cola varchar(5),
colb time
);


Inserting some records:

insert into fooo values
('a','10:00:00'), ('a','10:30:00'), ('b','20:00:00'), ('c','13:00:00')
, ('d','01:00:00'), ('e','10:10:00'), ('f','02:01:00'), ('g','02:20:00')
, ('h','04:00:00'), ('i','05:00:00'), ('j','06:00:00'), ('k','07:00:00')
, ('l','08:00:00'), ('m','09:00:00'), ('n','22:00:00'), ('o','23:00:00');


And the expected output should be looks like the following format:

Expected output:

cola 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
-------------------------------------------------------------------------------------------------
a 2
b 1
c 1
d 1
e 1
f 1
g 1
h 1
i 1
j 1
k 1
l 1
m 1
n 1
o 1

Answer
SELECT * FROM crosstab(
       'SELECT cola, EXTRACT(HOUR from colb) AS h, count(*) AS ct
        FROM   fooo
        GROUP  BY 1, 2
        ORDER  BY 1, 2'

       ,'SELECT g::float8 FROM generate_series(0,23) g'
   ) AS ct (cola text
      , h00 int, h01 int, h02 int, h03 int, h04 int, h05 int
      , h06 int, h07 int, h08 int, h09 int, h10 int, h11 int
      , h12 int, h13 int, h14 int, h15 int, h16 int, h17 int
      , h18 int, h19 int, h20 int, h21 int, h22 int, h23 int);

Asides:
You forgot the hour 0 / 24.
Using column names with leading character to obviate the need for double-quotes.

Details: