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

    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:
    PartNum__Yr-Mnth__Qty
    Part123___Jan03____88
    Part123___Feb03____33
    Part123___Mar03____06

    What I would like to output is:

    PartNum___Jan03__Feb03__Mar03
    Part123_____88_____33_____06

    Here is a (simple) example of my current SQL:
    Select PartNum, Date, Qty
    From Table1
    where
    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?

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    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
    Posts
    10
    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)


    select
    pm.HostPartID,
    pm.partcustom1,
    lt.loctypename,
    lm.loccustom5,
    lm.HostLocID,
    to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
    dh.historyamount,
    dh.historyschamount
    from
    DEMAND_HISTORY dh,
    PART_MASTER pm,
    LOCATION_MASTER lm,
    LOC_TYPE lt
    where
    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
    Location
    Dallas
    Posts
    76

    ....

    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
    Posts
    134

    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
  •