Re: Transposing rows and columns

On 09/16/2010 05:26 PM, Aram Fingal wrote:
>
> 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.
I think excel 2007 can handle more than 65,535 rows.
>
> 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

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve(dot)clark(at)netwolves(dot)com
www.netwolves.com