Postgres Crosstab Function

The crosstab function is used to produce “pivot” displays, wherein data is listed across the page rather than down.

For example, we might have data like

row1 val11
row1 val12
row1 val13
…
row2 val21
row2 val22
row2 val23
…

which we wish to display like

row1 val11 val12 val13 …
row2 val21 val22 val23 …
…

Here is different syntax to use crosstab in postgres

1) crosstab(text sql)

Example:

select * from crosstab(‘select rowid, attribute, value
from ct
order by 1,2’) as ct(row_name text, category_1 text, category_2 text);

The FROM clause must define the output as one row_name column (of the same data type as the first result column of the SQL query) followed by N value columns (all of the same data type as the third result column of the SQL query). You can set up as many output value columns as you wish. The names of the output columns are up to you.

The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. It fills the output value columns, left to right, with the value fields from these rows. If there are fewer rows in a group than there are output value columns, the extra output columns are filled with nulls; if there are more rows, the extra input rows are skipped.

2) crosstabN(text sql)
Example:

select * from crosstab2(‘select rowid, attribute, value
from ct
order by 1,2’)

By using this function there is no need to specify row_name and number of column_name in sql. Just replace N with number
of column_names.

Note: In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it’s just there to be ordered by, to control the order in which the third-column values appear across the page.

3) crosstab(text source_sql, text category_sql)
The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn’t work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

i) source_sql: is a SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column. It may also have one or more “extra” columns. The row_name column must be first. The category and value columns must be the last two columns, in that order. Any columns between row_name and category are treated as “extra”. The “extra” columns are expected to be the same for all rows with the same row_name value.

Example: SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

ii) category_sql: is a SQL statement that produces the set of categories. This statement must return only one column. It must produce at least one row, or an error will be generated. Also, it must not produce duplicate values, or an error will be generated.

Example: SELECT DISTINCT cat FROM foo ORDER BY 1;

Compelete SQL Query:
SELECT * FROM crosstab(‘SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1’, ‘SELECT DISTINCT cat FROM foo ORDER BY 1’) AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

If you want to see more detail please refer this link
http://www.postgresql.org/docs/9.1/static/tablefunc.html

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download