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)
</programlisting>

<programlisting>
CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)
</programlisting>
   </para>

   <para>
    You can create predefined functions to avoid having to write out
    the result column names and types in each query.  See the examples
    in the previous section.  The underlying C function for this form
    of <function>crosstab</function> is named <literal>crosstab_hash</literal>.
   </para>

  </sect3>

  <sect3 id="tablefunc-functions-connectby">
   <title><function>connectby</function></title>

   <indexterm>
    <primary>connectby</primary>
   </indexterm>

<synopsis>
connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])
</synopsis>

   <para>
    The <function>connectby</function> function produces a display of hierarchical
    data that is stored in a table.  The table must have a key field that
    uniquely identifies rows, and a parent-key field that references the
    parent (if any) of each row.  <function>connectby</function> can display the
    sub-tree descending from any row.
   </para>

   <para>
    <xref linkend="tablefunc-connectby-parameters"/> explains the
    parameters.
   </para>

   <table id="tablefunc-connectby-parameters">
    <title><function>connectby</function> Parameters</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Parameter</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry><parameter>relname</parameter></entry>
       <entry>Name of the source relation</entry>
      </row>
      <row>
       <entry><parameter>keyid_fld</parameter></entry>
       <entry>Name of the key field</entry>
      </row>
      <row>
       <entry><parameter>parent_keyid_fld</parameter></entry>
       <entry>Name of the parent-key field</entry>
      </row>
      <row>
       <entry><parameter>orderby_fld</parameter></entry>