Re: Transposing rows and columns

On Sep 16, 2010, at 4:37 PM, John R Pierce wrote:

> On 09/16/10 10:44 AM, Aram Fingal wrote:
>> 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 insist on this transposing, won't that mean you'll end up with more columns than SQL can/should handle?

No. The organization in Excel is much more efficient of the total number of cells used but not much good for querying. When I transpose it for use in the database (or pivot it in Excel), it actually multiplies the number of rows. So, if the version with separate columns for each subject has X rows and Y columns, you get X * Y rows in the database version. For example, If there are 100 subjects, and 1000 drug/dose combinations. Then the Excel version has 102 columns (drug, dose and a column for each subject) and 1000 rows. The database (or pivoted) version would have 4 columns (subject, drug, dose and response) and 100,000 rows. Excel maxes out at 65,535 rows and PostgreSQL has no limit.

The subjects, by the way, are not people, they are cancer cell tissue cultures in 384-well plates, handled by robots. That's how we can do so many drug/dose combinations. We'll do even more in the future.

-Aram