From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Advice on setting up a grid like view for spreadsheet users
Date: Fri, 10 Mar 2006 16:17:06 -0800
Message-ID: <44121702.9090203@obviously.com> (raw)
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
All;<br>
<br>
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:<br>
<br>
<table align="center" border="1" cellpadding="2" cellspacing="2"
width="80%">
<tbody>
<tr align="center">
<td colspan="5" rowspan="1" valign="top"><b>Report Type #1</b><br>
</td>
</tr>
<tr>
<td bgcolor="#cccccc" valign="top"><b>Date</b><br>
</td>
<td bgcolor="#cccccc" valign="top"><b>Location 1<br>
</b></td>
<td bgcolor="#cccccc" valign="top"><b>Location 2<br>
</b></td>
<td bgcolor="#cccccc" valign="top"><b>Location 3<br>
</b></td>
<td bgcolor="#cccccc" valign="top"><b>Location 4<br>
</b></td>
</tr>
<tr>
<td bgcolor="#cccccc" valign="top">Jan 2006<br>
</td>
<td bgcolor="#cccccc" valign="top">5<br>
</td>
<td bgcolor="#cccccc" valign="top">77<br>
</td>
<td bgcolor="#cccccc" valign="top">23<br>
</td>
<td bgcolor="#cccccc" valign="top">233<br>
</td>
</tr>
<tr>
<td bgcolor="#cccccc" valign="top">Feb 2006<br>
</td>
<td bgcolor="#cccccc" valign="top">7<br>
</td>
<td bgcolor="#cccccc" valign="top">556<br>
</td>
<td bgcolor="#cccccc" valign="top">233<br>
</td>
<td bgcolor="#cccccc" valign="top">269<br>
</td>
</tr>
<tr>
<td bgcolor="#cccccc" valign="top">March 2006<br>
</td>
<td bgcolor="#cccccc" valign="top">8<br>
</td>
<td bgcolor="#cccccc" valign="top">5666<br>
</td>
<td bgcolor="#cccccc" valign="top">1024<br>
</td>
<td bgcolor="#cccccc" valign="top">100<br>
</td>
</tr>
</tbody>
</table>
<br>
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).<br>
<br>
Now if I were just working in pure SQL, I'd build it in a relational
manner, with a long series of rows:<br>
<br>
<table align="center" border="1" cellpadding="2" cellspacing="2"
width="80%">
<tbody>
<tr>
<td valign="top"><b>report_type<br>
</b></td>
<td valign="top"><b>date<br>
</b></td>
<td valign="top"><b>xx_location<br>
</b></td>
<td valign="top"><b>value<br>
</b></td>
</tr>
<tr>
<td valign="top">1<br>
</td>
<td valign="top">Jan 2006<br>
</td>
<td valign="top">2<br>
</td>
<td valign="top">77<br>
</td>
</tr>
<tr>
<td valign="top">1<br>
</td>
<td valign="top">Feb 2006<br>
</td>
<td valign="top">2<br>
</td>
<td valign="top">556<br>
</td>
</tr>
<tr>
<td valign="top">3<br>
</td>
<td valign="top">Jan 2006<br>
</td>
<td valign="top">4<br>
</td>
<td valign="top">99.5<br>
</td>
</tr>
<tr>
<td valign="top">1<br>
</td>
<td valign="top">Jan 2006<br>
</td>
<td valign="top">1<br>
</td>
<td valign="top">5<br>
</td>
</tr>
<tr>
<td valign="top">3<br>
</td>
<td valign="top">Jan 2006<br>
</td>
<td valign="top">4<br>
</td>
<td valign="top">3.14159<br>
</td>
</tr>
</tbody>
</table>
<br>
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.<br>
<br>
</body>
</html>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce1@obviously.com
Subject: Re: Advice on setting up a grid like view for spreadsheet users
In-Reply-To: <44121702.9090203@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox