On Thu, Sep 16, 2010 at 01:44:30PM -0400, Aram Fingal wrote:
> On Sep 16, 2010, at 12:28 PM, Sam Mason wrote:
> > 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>.
It shouldn't give a row per subject as the subject isn't in the GROUP
BY list. Either that or you've got rounding problems that means that
the dose or drug combination means that they're effectively unique per
subject. You need to find the set of columns that gives you the "right"
number of rows, the same issue applies to pivot tables in Excel.
> > 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.
Maybe pull them out by experiment?
--
Sam http://samason.me.uk/