Re: 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