I'm working with some people who live and breath Excel. I need to be able to move data back and forth between formats
whichmake sense for Excel and for PostgreSQL. In some cases, this is just to accommodate what people are used to. In
othercases, like statistical clustering, it's something that really has to be done.
Here is a simplified example:
I'm given data in Excel with one sheet each for a bunch of experiments. In each sheet, there are rows with different
drugsat different doses and columns for each subject. The cells contain the response data. I wrote a Perl script
whichautomates the process of extracting that data into a csv file which can be imported into a table like the
following:
create table results(
expt_no int references experiments(id),
subject int references subjects(id),
drug text references drugs(name),
dose numeric,
response numeric
)
Now, suppose I do some computation on the results in the database and want to export it back out to the same kind of
formatthat I received it (drugs and doses in rows and subjects in columns.) One method would be to use Perl. I could
useDBD::Pg and loop through a bunch of queries to build a two dimensional array and then spit that back out but is
therea good way to do this just in SQL? Is there a better way than creating a temporary table for each subject and
thenjoining all the temp tables?
-Aram