INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2') INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1') 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.ĬREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT)
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. 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. It fills the output value columns, left to right, with the value fields from these rows. The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. The names of the output columns are up to you. You can set up as many output value columns as you wish. 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). This example produces a set something like: SELECT * FROM crosstab('.') AS ct(row_name text, category_1 text, category_2 text) The crosstab function is declared to return setof record, so the actual names and types of the output columns must be defined in the FROM clause of the calling SELECT statement, for example: N is an obsolete parameter, ignored if supplied (formerly this had to match the number of output value columns, but now that is determined by the calling query).įor example, the provided query might produce a set something like: This statement must return one row_name column, one category column, and one value column.
The sql parameter is an SQL statement that produces the source set of data. The crosstab function takes a text parameter that is an SQL query producing raw data formatted in the first way, and produces a table formatted in the second way.
The crosstab function is used to produce “ pivot” displays, wherein data is listed across the page rather than down. Produces a representation of a hierarchical tree structure. The parameter N is now ignored, since the number of value columns is always determined by the calling query.Ĭonnectby ( relname text, keyid_fld text, parent_keyid_fld text, start_with text, max_depth integer ) → setof record Produces a “ pivot table” with the value columns specified by a second query.Ĭrosstab ( sql text, N integer ) → setof record crosstab2, crosstab3, and crosstab4 are predefined, but you can create additional crosstab N functions as described below.Ĭrosstab ( source_sql text, category_sql text ) → setof record
#Postgresql crosstab plus
Produces a “ pivot table” containing row names plus N value columns. Produces a “ pivot table” containing row names plus N value columns, where N is determined by the row type specified in the calling query.Ĭrosstab N ( sql text ) → setof table_crosstab_ N Produces a set of normally distributed random values. Normal_rand ( numvals integer, mean float8, stddev float8 ) → setof float8
0 kommentar(er)
