Re: Transposing rows and columns

On Thu, Sep 16, 2010 at 11:42:21AM -0400, Aram Fingal wrote:
> create table results(
> expt_no int references experiments(id),
> subject int references subjects(id),
> drug text references drugs(name),
> dose numeric,
> response numeric
> )

What's the primary key? I presume it's (expt_no,subject,drug,dose).

> Now, suppose I do some computation on the results in the database and
> want to export it back out to the same kind of format that I received
> it (drugs and doses in rows and subjects in columns.)

Have you tried setting up an ODBC data source to the database and use
the PivotTable functionality in Excel to do the transformation?

If you want to do the transformation in SQL, you'd be writing something
like:

SELECT drug, dose
MIN(CASE subject WHEN 1 THEN response END) AS resp_1,
MIN(CASE subject WHEN 2 THEN response END) AS resp_2,
MIN(CASE subject WHEN 3 THEN response END) AS resp_3
FROM results
WHERE expt_no = 1
AND subject IN (1,2,3)
GROUP BY drug, dose
ORDER BY drug, dose;

Or you can use the tablefunc contrib module as suggested by Uwe. I
prefer doing it by hand as you get more options, but it can be quite
tedious if you've got lots of columns you're trying to deal with.

If I've got my assumption about primary key wrong then my code, as well
as the tablefunc, will probably both fail to do the "right thing".

--
Sam http://samason.me.uk/