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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
