Commented by: @dyemanov

Unfortunately, this is against the whole FB architecture. By design, the result set width is fixed at the moment of prepare.

Commented by: @asfernandes

The MSSQL PIVOT defines fixed columns. So we can implement it in the same manner.

Commented by: Pabloj (pabloj_sourceforge)

Should be closed as duplicate of CORE650

Commented by: Luke (lplatypus)

Oracle 11g also introduced a PIVOT clause like what was introduced in MSSQL 2005

MSSQL 2005 docs: http://msdn.microsoft.com/en-us/library/ms177410.aspx
Oracle article: http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
Oracle reference docs: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#sthref6753

This is not a duplicate of CORE650 (SELECT ... GROUP BY ... WITH CUBE) as that query does not directly produce a cross table (though it was a common way to gather the data required to build a cross table prior to the introduction of the PIVOT syntax).

Commented by: Ivan (patuljak)

postgresql has Crosstab
http://www.postgresql.org/docs/current/static/tablefunc.html

example from postgresql

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
'select year, month, qty from sales order by 1',
'select m from generate_series(1,12) m'
) as (
year int,
"Jan" int,
"Feb" int,
"Mar" int,
"Apr" int,
"May" int,
"Jun" int,
"Jul" int,
"Aug" int,
"Sep" int,
"Oct" int,
"Nov" int,
"Dec" int
);
year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000
2008 | 1000 | | | | | | | | | | |
(2 rows)

Commented by: @asfernandes

Current (3.0) external procedures allows the creation of (very) similar feature as showed by Ivan.

Commented by: Marcin (wodzu)

I was hoping that this feature will be implemented in 3.0. "PIVOT" has a crucial meaning in structures like EAV: http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Adriano I do not see how external procedures would help in creating such result set, will they allow to return different number of columns each time they are called?

Commented by: @asfernandes

If I may recommend you something, I'd say stay away from EAV. ;)

PIVOT (as in Oracle) is defined when a query is compiled. It may not change the columns depending on the data.

Re. external procedures, you'd need to define an external procedure for each query (result columns), while may passing the same external procedure name for each of them. External procedures can inspect the object metadata being called and known what columns (output parameters) were declared.