• 1 reply
  • Latest Post - ‏2007-03-12T23:40:15Z by Jean_Anderson
1525 Posts

Pinned topic Need help with SQL Query

‏2007-03-12T21:19:40Z |
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)
Updated on 2007-03-12T23:40:15Z at 2007-03-12T23:40:15Z by Jean_Anderson
  • Jean_Anderson
    179 Posts

    Re: Need help with SQL Query

    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, 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.