Advice on setting up a grid like view for spreadsheet users - Mailing list pgsql-sql
| From | Bryce Nesbitt |
|---|---|
| Subject | Advice on setting up a grid like view for spreadsheet users |
| Date | |
| Msg-id | 44121702.9090203@obviously.com Whole thread Raw |
| List | pgsql-sql |
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 setit up in postgres. The resulting view has to be spreadsheet-like, and will be loaded directly via ODBC into Excel fornumber 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><tdbgcolor="#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>Location3<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><tdbgcolor="#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><tdbgcolor="#cccccc" valign="top">100<br /></td></tr></tbody></table><br /> I could mirror that exact structure ina 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 /><tablealign="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><tdvalign="top"><b>value<br /></b></td></tr><tr><td valign="top">1<br /></td><td valign="top">Jan 2006<br /></td><tdvalign="top">2<br /></td><td valign="top">77<br /></td></tr><tr><td valign="top">1<br /></td><td valign="top">Feb2006<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><tdvalign="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><tdvalign="top">3.14159<br /></td></tr></tbody></table><br /> It would be relational, but I don't know how to convertit to a sorted and grid-like "view", without a (perl) script. Any thoughts on this? One thought is to swap the rowsand columns. But then I have dates as the column names, which seems inelegant.<br /><br />