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
alsoreturns a separate row for each drug/dose/subject combination which isn't exactly what I want. Each row has one
columnwith 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