Thread: sql question

Jodi Kanter wrote:
> Can someone tell me how to create a query that will list values in a
> field across columns instead of listing them in row form. Can this be
> done in one sql query? Thanks Jodi

Are you referring to a crosstab, i.e.:

select * from cth;
   id | rowid |        rowdt        |   attribute    |      val
  ----+-------+---------------------+----------------+---------------
    1 | test1 | 2003-03-01 00:00:00 | temperature    | 42
    2 | test1 | 2003-03-01 00:00:00 | test_result    | PASS
    3 | test1 | 2003-03-01 00:00:00 | volts          | 2.6987
    4 | test2 | 2003-03-02 00:00:00 | temperature    | 53
    5 | test2 | 2003-03-02 00:00:00 | test_result    | FAIL
    6 | test2 | 2003-03-02 00:00:00 | test_startdate | 01 March 2003
    7 | test2 | 2003-03-02 00:00:00 | volts          | 3.1234
(7 rows)

SELECT * FROM crosstab(
    'SELECT rowid, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, temperature int4, test_result text, test_startdate
timestamp, volts float8);
   rowid | temperature | test_result |   test_startdate    | volts
  -------+-------------+-------------+---------------------+--------
   test1 |          42 | PASS        |                     | 2.6987
   test2 |          53 | FAIL        | 2003-03-01 00:00:00 | 3.1234
(2 rows)

There is an older version of the crosstab function above, in PostgreSQL
7.3.x, in contrib/tablefunc. The one shown above is a significant
improvement that should be in 7.4 when it's released, but it will work
fine with 7.3.x. If you want a copy of the updated version, you can get
it here:

   http://www.joeconway.com/

There are some pure SQL ways to do this also; search the list archives
for the sql and general lists:

   http://archives.postgresql.org/pgsql-sql/
   http://archives.postgresql.org/pgsql-general/

HTH,

Joe