Databases
StackOverflow2013
Postsdbo
POSplitting data into a per-year basis using SQL with pivots
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSplitting data into a per-year basis using SQL with pivots
    primarykey
    data
    text
    <p>I'm having some trouble figuring out how construct a series of SQL statements to split some data I have into a per-year basis. There are n items which have a recorded quantity for a set of days across so many years. Here's is a a small sample layout for the main data I have.</p> <pre><code> |========|==============|===========| | Name | Date | Quantity | |========|==============|===========| 1 | AAAAA | 10-DEC-2008 | 5 | 2 | AAAAA | 11-DEC-2008 | 2 | 3 | AAAAA | 12-DEC-2008 | 0 | 4 | AAAAA | 09-DEC-2009 | 3 | 5 | AAAAA | 10-DEC-2009 | 2 | 6 | AAAAA | 11-DEC-2009 | 3 | 7 | BBBBB | 10-DEC-2008 | 5 | 8 | BBBBB | 11-DEC-2008 | 2 | 9 | BBBBB | 12-DEC-2008 | 0 | 10 | BBBBB | 09-DEC-2009 | 3 | 11 | BBBBB | 10-DEC-2009 | 1 | 12 | BBBBB | 11-DEC-2009 | 0 | |========|==============|===========| </code></pre> <p>I need to convert this into a table which takes the form.</p> <pre><code> |========|==============|===============|===============| | Name | Date | Quantity 2008 | Quantity 2009 | |========|==============|===============|===============| 1 | AAAAA | 09-DEC | | 3 | 2 | AAAAA | 10-DEC | 5 | 2 | 3 | AAAAA | 11-DEC | 2 | 3 | 4 | AAAAA | 12-DEC | 0 | | 5 | BBBBB | 09-DEC | | 3 | 6 | BBBBB | 10-DEC | 5 | 1 | 7 | BBBBB | 11-DEC | 2 | 0 | 8 | BBBBB | 12-DEC | 0 | | |========|==============|===============|===============| </code></pre> <p>It should be assumed that there are thousands of named items in the database and the start days of recording on each year will be different, E.G., 6th Dec 2010 and 5th Dec 2009.</p> <p>Hope someone can help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLewis Norton
    1. USLewis Norton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSplitting data into a per-year basis using SQL with pivots
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSplitting data into a per-year basis using SQL with pivots
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhenever you want to convert columns to rows or vice versa, look into pivot/unpivot (explicitly on 11g, or "techniques" that mimic an explicit function on earlier versions).
      singulars
      1. POSplitting data into a per-year basis using SQL with pivots
      1. UStbone
    2. CO@tbone, Seems the way to go.
      singulars
      1. POSplitting data into a per-year basis using SQL with pivots
      1. USLewis Norton
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload