Re: Transposing rows and columns

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

In response to

Responses

Browse pgsql-general by date

  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