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)
This topic has been locked.
1 reply Latest Post - 2007-03-12T23:40:15Z by Jean_Anderson
Pinned topic Need help with SQL Query
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2007-03-12T23:40:15Z at 2007-03-12T23:40:15Z by Jean_Anderson
Jean_Anderson 120000E4UY179 PostsACCEPTED ANSWER
Re: Need help with SQL Query2007-03-12T23:40:15Z in response to SystemAdminIt 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.