have table A which contains a primary key (counter integer), Value (REAL) and date (DATE)
there are up to 365(366) rows, one for each day of the year
each row has a data value, a date, and a id (counter - primary key)
I need to query (select as) the data (possibly to a new table, would prefer not to use a new table) so that each row returned by the query has the day of the month (1,2, 3, etc.) as the first field and twelve data fields containing data values (one for each month)
If a new table is necessary, the new table would have field names of: Day INTEGER, Jan CHAR(3), Feb CHAR(3), .... Dec CHAR(3)(total of 13 fields - columns)
No data available, default to null
The end result should look like this
Day Jan Feb Mar Apr .... Dec
1 3222.1 4256.1 3458.1 2314.9 1347.8 (row 1)
2 2345.7 3247.5 4567.1 2356.1 3458.1 (row 2)
31 2356.6 3456.9 2356.8 2345.1 2345.1 (row 31)
Jean_Anderson 120000E4UY179 Posts
Re: Need help with SQL Query2007-03-12T23:40:15ZThis is the accepted answer. This is the accepted answer.It sounds to me like you want to do a crosstab report.
I recommend Joe Celko's "SQL for Smarties: Advanced SQL Programming". In my 1995 edition section 21.6 covers "Cross Tabulations", which shows a way to do cross tabs using standard SQL. I checked amazon.com, and this book is still available (and into its third edition: 2005).
Even if somebody can post a solution for what you want to do, I still highly recommend this book.