id="tablefunc-functions-crosstab-text">
   <title><function>crosstab(text)</function></title>

   <indexterm>
    <primary>crosstab</primary>
   </indexterm>

<synopsis>
crosstab(text sql)
crosstab(text sql, int N)
</synopsis>

   <para>
    The <function>crosstab</function> function is used to produce <quote>pivot</quote>
    displays, wherein data is listed across the page rather than down.
    For example, we might have data like
<programlisting>
row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...
</programlisting>
    which we wish to display like
<programlisting>
row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...
</programlisting>
    The <function>crosstab</function> 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.
   </para>

   <para>
    The <parameter>sql</parameter> parameter is an SQL statement that produces
    the source set of data. This statement must return one
    <structfield>row_name</structfield> column, one
    <structfield>category</structfield> column, and one
    <structfield>value</structfield> column.  <parameter>N</parameter> 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).
   </para>

   <para>
    For example, the provided query might produce a set something like:
<programlisting>
 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8
</programlisting>
   </para>

   <para>
    The <function>crosstab</function> function is declared to return <type>setof
    record</type>, so the actual names and types of the output columns must be
    defined in the <literal>FROM</literal> clause of the calling <command>SELECT</command>
    statement, for example:
<programlisting>
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
</programlisting>
    This example produces a set something like:
<programlisting>
           &lt;== value  columns  ==&gt;
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6
</programlisting>
   </para>

   <para>
    The <literal>FROM</literal> clause must define the output as one
    <structfield>row_name</structfield> column (of the same data type as the first result
    column of the SQL query) followed by N <structfield>value</structfield> 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.
   </para>

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

   <para>
    In practice the SQL query should always specify <literal>ORDER BY 1,2</literal>
    to ensure that the input rows are properly ordered, that is, values with
    the same <structfield>row_name</structfield> are brought together and
    correctly ordered within the row.  Notice that <function>crosstab</function>
    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