PostgreSQL - crosstab

PostgreSQL-crosstab

/*CREATE EXTENSION tablefunc*/

INSERT INTO functions_header VALUES 
('AM'),
('CM'),
('NM');


SELECT * FROM crosstab(
'
select x.name,substring(x.func from 1 for 2),sum(x.w) from (
		(select c.name, f.name as func, sum(lg.weight) as w from	
			logentry lg
			join keyword as k on (lg.keyword_id = k.id)
			join company as c on (lg.company_id = c.id)
			join function as f on (k.function_id = f.id)

			where c.numberofoccurrences > 5

			group by c.name,f.name)
		union all
		(select c.name, f.name as func, 0 as w from
			function as f,
			company as c

			where c.numberofoccurrences > 5
			
			group by c.name,f.name)
	    
	) as x
	group by x.name, substring(x.func from 1 for 2)
	order by x.name, substring(x.func from 1 for 2)
',
'SELECT * FROM functions_header')
as
(company text,
AM text,
CM text,
NM text)