X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2A9D09DC841 for ; Fri, 10 Mar 2006 20:17:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81520-03 for ; Fri, 10 Mar 2006 20:17:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outbound.internet-mail-service.net (outbound6.internet-mail-service.net [216.240.47.197]) by postgresql.org (Postfix) with ESMTP id C5C2E9DC805 for ; Fri, 10 Mar 2006 20:17:10 -0400 (AST) Received: from idiom.com (idiom.com [216.240.32.1]) by outbound.internet-mail-service.net (Postfix) with ESMTP id 6BD0E2411CF for ; Fri, 10 Mar 2006 16:17:14 -0800 (PST) Received: from [192.168.1.109] (adsl-69-107-132-69.dsl.pltn13.pacbell.net [69.107.132.69]) by idiom.com (8.12.11/8.12.11) with ESMTP id k2B0HDu2003515 for ; Fri, 10 Mar 2006 16:17:14 -0800 (PST) (envelope-from bryce1@obviously.com) Message-ID: <44121702.9090203@obviously.com> Date: Fri, 10 Mar 2006 16:17:06 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Advice on setting up a grid like view for spreadsheet users Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.204 required=5 tests=[AWL=0.076, HTML_MESSAGE=0.001, HTML_TAG_EXIST_TBODY=0.126, MIME_HTML_ONLY=0.001] X-Spam-Score: 0.204 X-Spam-Level: X-Archive-Number: 200603/154 X-Sequence-Number: 24500 All;

I have a need to create a view. The data table does not exist yet, the question is on how to best set it up in postgres. The resulting view has to be spreadsheet-like, and will be loaded directly via ODBC into Excel for number crunching. Maybe something like:

Report Type #1
Date	Location 1	Location 2	Location 3	Location 4
Jan 2006	5	77	23	233
Feb 2006	7	556	233	269
March 2006	8	5666	1024	100

I could mirror that exact structure in a SQL table, but I'd loose the relation (since the column headings actually refer to an associated table).

Now if I were just working in pure SQL, I'd build it in a relational manner, with a long series of rows:

report_type	date	xx_location	value
1	Jan 2006	2	77
1	Feb 2006	2	556
3	Jan 2006	4	99.5
1	Jan 2006	1	5
3	Jan 2006	4	3.14159

It would be relational, but I don't know how to convert it to a sorted and grid-like "view", without a (perl) script. Any thoughts on this? One thought is to swap the rows and columns. But then I have dates as the column names, which seems inelegant.