On Sep 16, 2010, at 12:28 PM, Sam Mason wrote: > 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).
Yes that's correct. I copied and simplified from the actual table, which has a lot more in the table definition. > >> 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? I have thought about that but later on, when we do the full sized experiments, there will be too many rows for Excel to handle. > > 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; That's a good trick to know but I just tried it and found that it begins to get complicated with the actual data. It also returns a separate row for each drug/dose/subject combination which isn't exactly what I want. Each row has one column with a value and the rest of the columns in that row are all <null>. > > 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. I guess I need to read up on pivot tables. At first glance, this looks like the best solution. -Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
