Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: pivot rows into columns

    I have looked up as much as I can, but my head is ready to explode.

    I have output like this:
    PHP Code:
    CUST    DETAIL          INTERVAL

    Bender  .98  Breakdown 8
    /5/2003 4:00:00 PM
    Diblasi 0              8
    /5/2003 4:00:00 PM
    Bender  0              8
    /5/2003 4:15:00 PM
    Diblasi 0              8
    /5/2003 4:15:00 PM
    Bender  0              8
    /5/2003 4:30:00 PM
    Diblasi 0              8
    /5/2003 4:30:00 PM
    Bender  0              8
    /5/2003 4:45:00 PM
    Diblasi 0              8
    /5/2003 4:45:00 PM 
    I would like to pivot the Interval so it groups and goes to the top
    and then group the customers with their data going across.

    Example:
    PHP Code:
    CUST    8/5/2003 4:00:00 PM          8/5/2003 4:15:00 PM

    Bender  .98  Breakdown               0
    Diblasi 0                            0 
    You get the idea, but I am at a loss how to do it.

    I have the following query which gets the output I supplied above:
    Code:
    SELECT
      c.lname_txt||' '||ci.eim_serial_id AS CUSTOMER,
      SUM(ci.on_pct_nbr) / 100||'  '||
      (CASE 
    	  	WHEN p.end_dt > ci.end_dt AND SUM(ci.on_pct_nbr) > 0 THEN 'Breakdown' 
    		WHEN p.override_dt > ci.end_dt AND SUM(ci.on_pct_nbr) > 0  THEN 'Breakdown' 
    		WHEN p.override_dt < ci.end_dt AND SUM(ci.on_pct_nbr) > 0 THEN 'Override' 
    		WHEN p.end_dt < ci.end_dt THEN 'Completed' END) AS USAGE,
      MIN(ci.end_dt-(4/24)) AS INTERVAL
    FROM 
      dr_evt_participant p,
      gateway_premise gp,
      eim e,
      compressor_interval ci,
      customer c
    WHERE 
      p.org_id = 'ME' AND
      p.event_id = '03080500' AND
      p.conflict_ind IS NULL AND
      p.start_dt IS NOT NULL AND
      gp.org_id = p.org_id AND
      gp.cust_id = p.cust_id AND
      e.org_id = gp.org_id AND
      e.gateway_id = gp.gateway_id AND
      ci.org_id = e.org_id AND
      ci.eim_serial_id = e.eim_serial_id AND
      ci.end_dt >= TO_DATE('080503 2000', 'MMDDYY HH24MI') AND
      ci.end_dt <= TO_DATE('080603 0015', 'MMDDYY HH24MI') AND
      gp.org_id = c.org_id AND
      gp.cust_id = c.cust_id 
    GROUP BY 
      ci.eim_serial_id,p.end_dt,
      c.lname_txt,
      p.OVERRIDE_DT,
      ci.end_dt   
    ORDER BY 
      ci.end_dt, c.lname_txt ASC;
    thanks in advance.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    First create a table with results from your query:

    Drop Table Interval_Detail;
    Create Table Interval_Detail
    As
    Select ...
    <<... here goes your query ...>>
    /

    Then create the 'pivot' procedure:

    Create Or Replace Procedure Pivot_Interval
    Is
    S Varchar2(5000);
    T Varchar2(5000);
    C Integer;
    I Integer;
    Begin
    S:= 'Insert Into Pivot_Interval Select Customer ';
    T:= 'Create Table Pivot_Interval (Customer Varchar2(18)';
    For Rec In (Select Distinct Interval From Interval_Detail)
    Loop
    S:= S || ', Decode(Interval, ''' || Rec.Interval ||''', Detail, 0)';
    T:= T || ', "' || Rec.Interval || '" Varchar2(20)';
    End Loop;
    S:= S || 'From Interval_Detail';
    T:= T || ')';
    Begin
    Select Count(*) Into I
    From User_Tables
    Where Table_Name = 'PIVOT_INTERVAL';
    If I>0 Then
    Execute Immediate 'Drop Table Pivot_Interval';
    End If;
    Execute Immediate T;
    Execute Immediate S;
    Exception
    When Others Then
    Raise;
    End;
    Commit;
    End;
    /

    Then, execute the procedure:

    exec pivot_interval;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    If this works I am gonna buy you a beer.
    I'll test it tomorrow.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    PS:

    The oracle user id which exxecutes the procedure needs the following system permissions:

    DROP ANY TABLE and CREATE ANY TABLE
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    If you want one customer per line, then change the following lines:

    S:= S || ', MAX(Decode(Interval, ''' || Rec.Interval ||''', Detail, 0))';
    ...
    S:= S || 'From Interval_Detail Group By Customer'
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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