<!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>