Display multiple values of a column in one row in PostgreSQL

Question

I have a query like this:

select to_date(to_char(registime, 'YYYY-MM'),'YYYY-MM') as dt,count(id) as total_call  
from all_info 
where alarm_id is null
group by dt   
order by dt

And the result just like this:

dt          total_call      
2011-03-01     45
2011-04-01     61
2011-05-01     62
2011-06-01     41
2011-07-01     48
2011-08-01     42
2011-09-01     28
2011-10-01     39

I want the result like in the demo below, a table form:

2011-03-01   2011-04-01  2011-05-01  2011-06-01  ..........
   45            61          62          41

I want to use crosstab, but it doesn't seem to work?

Answer 1

Look into the contrib module tablefunc. It provides exactly the kind of pivot table functionality you are looking for. See the manual here.

Follow the installation instructions here or in the article @Paul Tomblin suggested in his comment above.

Then your function could look like this:

SELECT *
  FROM crosstab($$
SELECT 'total_calls'::text AS col_name
      ,to_char(registime, '"x"YYYY_MM_01') as dt
      ,count(id) as total_call  
FROM   all_info 
WHERE  alarm_id is null
GROUP  BY dt   
ORDER  BY dt
$$)
AS ct(
 call_day text
,x2011_03_01 int8
,x2011_04_01 int8
,x2011_05_01 int8
,x2011_06_01 int8
,x2011_07_01 int8
,x2011_08_01 int8
,x2011_09_01 int8
,x2011_10_01 int8);

Output:

  call_day   | x2011_03_01 | x2011_04_01 | x2011_05_01 | x2011_06_01 | x2011_07_01 | x2011_08_01 | x2011_09_01 | x2011_10_01
-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------
 total_calls |           1 |           4 |           4 |           2 |           1 |           5 |           1 |           1

Column names can't start with a digit (or you have to double-quote), that's why I prefixed the date with x. I also simplified your query.
You could wrap this in a view or function for repeated use.
Maybe a plpgsql function that dynamically adjusts the column names and EXECUTEs.

More details, explanation and links in this related answer.