Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: Creating Output in columns instead of Rows

    Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

    Right now my output would look like this:

    What I would like to output is:


    Here is a (simple) example of my current SQL:
    Select PartNum, Date, Qty
    From Table1
    Date >= To_Date('01/01/2003','mm/dd/yyyy') and
    Date <= To_Date('01/31/2004','mm/dd/yyyy')
    Order by PartNum, Date

    (Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

    I have seen an example of PIVOT, but could not get that to work.

    Any suggestions?


  2. #2
    Join Date
    Oct 2003

    Re: Creating Output in columns instead of Rows

    select Distinct PartNum,
    cast(0 as decimal(15,0)) as Jan03,
    cast(0 as decimal(15,0)) as Feb03,
    cast(0 as decimal(15,0)) as Mar03,
    into #temp
    from Table1

    Then from there run your regular query, put in a temp table.

    Then from there you can update the columns in the above table.

    Its ugly but works.

    You can also do subselects.

    Hope this gets your mind rolling

  3. #3
    Join Date
    Feb 2004
    Thanks for the reply. Guess I should not have made my example so simple!

    The date actually comes from (part of select statement):
    to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

    So the "field" Yr-Mnth is not a table field, but created through the select statement.

    The Qty comes from:
    (dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
    There is only 1 value per month for each value.

    In this example, the output would have 13 columns of demand data, 1 listed for each month.

    As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

    Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
    If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

    to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
    LOC_TYPE lt
    pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
    (dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
    order by
    pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

    (I don't have to use (+) in my table joins as there will always be a match)

    Guess placing the data into columns instead of rows is not as simple as I had hoped!?!

  4. #4
    Join Date
    Oct 2003


    well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

    Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

    You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

    Let me know Monday if you have not figured it out, I am leaving the office.

  5. #5
    Join Date
    Dec 2002

    Re: Creating Output in columns instead of Rows

    If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

    select col1, col2,
    sum(case when month(datecol1)=1 then value1 else 0 end) month1,
    sum(case when month(datecol1)=12 then value1 else 0 end) month12
    from table1 .....
    where ...
    group by col1, col2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts