Обсуждение: exporting join results

I have some tables that I am gathering data in.  Some of the data
gathered goes into personal table and the survey results goes into the
survey table.

I can do a join with no problem to see my results with something like:

select * from personal,survey WHERE personal.pid=survey.pid;

I can do a dump of either table again with no problem.

What I want is to get a dump of the join as a comma separated text file
so that I can import it into my statistical software.

Any ideas on how to do it?

Thanks for the help.

--
Dr. Roy F. Cabaniss
Associate Professor of Marketing
University of Arkansas Monticello
http://cabanisspc.uamont.edu

Either create a view and access it from an spreadsheet via ODBC, or SELECT INTO
another table, then dump it.

Good luck.

Fernando.

En un mensaje anterior, Roy Cabaniss escribió:
> I have some tables that I am gathering data in.  Some of the data
> gathered goes into personal table and the survey results goes into the
> survey table.
>
> I can do a join with no problem to see my results with something like:
>
> select * from personal,survey WHERE personal.pid=survey.pid;
>
> I can do a dump of either table again with no problem.
>
> What I want is to get a dump of the join as a comma separated text file
> so that I can import it into my statistical software.

On 29 May 2003, Roy Cabaniss wrote:

> I have some tables that I am gathering data in.  Some of the data
> gathered goes into personal table and the survey results goes into the
> survey table.
>
> I can do a join with no problem to see my results with something like:
>
> select * from personal,survey WHERE personal.pid=survey.pid;
>
> I can do a dump of either table again with no problem.
>
> What I want is to get a dump of the join as a comma separated text file
> so that I can import it into my statistical software.
>
> Any ideas on how to do it?

join into another table, then export that table.

On Thu, May 29, 2003 at 10:54:37 -0500,
  Roy Cabaniss <rcaban@cabanisspc.uamont.edu> wrote:
> I have some tables that I am gathering data in.  Some of the data
> gathered goes into personal table and the survey results goes into the
> survey table.
>
> I can do a join with no problem to see my results with something like:
>
> select * from personal,survey WHERE personal.pid=survey.pid;
>
> I can do a dump of either table again with no problem.
>
> What I want is to get a dump of the join as a comma separated text file
> so that I can import it into my statistical software.
>
> Any ideas on how to do it?

You can concatenate the columns you want with commas and quotes as
necessary. Also you should use coalesce to handle any null values.

If you don't want to deal with another table that
holds all of your data, you could export the data
using a query with a few psql commands.

\a  -- unaligned output, i.e. without space padding
\f ','  -- specify comma field separator
\t    -- tuples (i.e. data) only, if that is what you
want
\o filename  -- destination file for your data

Now run your query, and you should have what you want.
 Shouldn't be too hard to make a script out of that.

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

On Thu, 2003-05-29 at 11:07, Fernando Schapachnik wrote:
> Either create a view and access it from an spreadsheet via ODBC, or SELECT INTO
> another table, then dump it.
>
> Good luck.
>

The select into worked quite well.  It would have been cool to have
gotten the joined structure along with the csv dump but I can deal with
it as it came.

(the invocation of psql gave an unknown -- error and I was sure that the
select into would do the trick so I didn't try to track down the error)

Thanks to all that helped.

--
Roy Cabaniss <rcaban@cabanisspc.uamont.edu>
Cabaniss Class