From: | Aram Fingal <fingal(at)multifactorial(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transposing rows and columns |
Date: | 2010-09-16 17:44:30 |
Message-ID: | CC84AF33-331F-4773-A7E2-C49130F91C57@multifactorial.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Hopfgartner | 2010-09-16 18:36:17 | Re: Getting FATAL: terminating connection due to administrator command |
Previous Message | Christine Penner | 2010-09-16 17:33:53 | Re: query join issue |