Split query results from one row to 5 rows

Hi,
How can I change the below SQL returning one row showing all 5 aging values
ACCOUNT_NUMBER, PARTY_NAME, "0-30", "31-60", "61-90", "91-120", ">120"

 into 5 rows showing one age range each row?
 ACCOUNT_NUMBER, PARTY_NAME, "0-30"
 ACCOUNT_NUMBER, PARTY_NAME,  "31-60"
 ACCOUNT_NUMBER, PARTY_NAME,  "61-90"
 ACCOUNT_NUMBER, PARTY_NAME,  "91-120"
 ACCOUNT_NUMBER, PARTY_NAME,  ">120"

select 
  "ACCOUNT_NUMBER",
 "PARTY_NAME",
 "0-30",
 "31-60",
 "61-90",
 "91-120",
 ">120"
from AR_AGING_SUM_VW 
where CUST_ACCOUNT_ID = 1234 

Open in new window

LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
 
sdstuberCommented:
If you can't use the underlying tables, or if they are not an unpivoted source then try this....  (should work any version 9i or later.  Essentially the same as UNPIVOT but that requires 11g)


SELECT account_number,
       party_name,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 1 THEN "0-30"
           WHEN n = 2 THEN "31-60"
           WHEN n = 3 THEN "61-90"
           WHEN n = 4 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120"
          FROM ar_aging_sum_vw,
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE cust_account_id = 1234)
0
 
lcohanDatabase AnalystCommented:
You need to use  a function like PIVOT/UNPIVOT:
http://www.oracle-developer.net/display.php?id=506
0
 
sdstuberCommented:
by any chance, is your data already unpivoted in the tables underlying the view?

can you use them and simply group by the criteria you need?

what I'm getting at is you might have a query that is doing a pivot just so you can unpivot when done.  That's clearly not efficient.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
BILL CarlisleAPEX DeveloperAuthor Commented:
Good point Sean.. I'll check
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Wow, it is a bunch of case statements

select trx_number,due_date,CUSTOMER_ID,
       aging_between_0_30 "0-30",
       aging_between_31_60 "31-60",
       aging_between_61_90 "61-90",
       aging_between_91_120 "91-120",
       aging_greater_than_120 ">120",
       ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) Total
       from (select trx_number,due_date,CUSTOMER_ID, sum(aging_between_0_30) aging_between_0_30,
               sum(aging_between_31_60) aging_between_31_60,
               sum(aging_between_61_90) aging_between_61_90,
               sum(aging_between_91_120) aging_between_91_120,
               sum(aging_greater_than_120) aging_greater_than_120        
          FROM (SELECT rcta.trx_number,apsa.due_date,apsa.CUSTOMER_ID,
                   (CASE
                         WHEN (SYSDATE - apsa.due_date) BETWEEN -100 AND 30 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_0_30,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 31 AND 60 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_31_60,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 61 AND 90 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_61_90,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) between 91 and 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_between_91_120,                       
                       (CASE
                         WHEN (SYSDATE - apsa.due_date) > 120 THEN
                          apsa.amount_due_remaining
                         ELSE
                          0
                       END) aging_greater_than_120                     
                  from ra_customer_trx_all       rcta,
                       ar_payment_schedules_all  apsa
                 WHERE apsa.customer_trx_id      = rcta.customer_trx_id
                 --AND   rcta.bill_to_customer_id  = 3347
                 ) GROUP BY trx_number,due_date,CUSTOMER_ID)
                 WHERE ((aging_between_0_30) + (aging_between_31_60) + ((aging_between_61_90) + (aging_between_91_120) + (aging_greater_than_120))) > 0
                 ORDER BY 8 DESC;

Open in new window

0
 
sdstuberCommented:
can you use the raw data or are you required (either by business rule or privileges) to use the view?

can we assume this?

trx_number = account_number
CUSTOMER_ID = party_name
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
sorry wrong view
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
no that was right.. :)
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Beautiful...

SELECT account_number,
       PARTY_NAME,
       CASE WHEN n = 150 THEN '>120' ELSE (n - 30) || '-' || n END agerange,
       CASE
           WHEN n = 30 THEN "0-30"
           WHEN n = 60 THEN "31-60"
           WHEN n = 90 THEN "61-90"
           WHEN n = 120 THEN "91-120"
           ELSE ">120"
       END
           age
  FROM (SELECT "ACCOUNT_NUMBER",
               "PARTY_NAME",
               "0-30",
               "31-60",
               "61-90",
               "91-120",
               ">120", n
-- select * 
          FROM "APPS"."XXAPEX_DB_05_AR_AGING_SUM",
               (    SELECT LEVEL * 30 n
                      FROM DUAL
                CONNECT BY LEVEL <= 5)
         WHERE CUST_ACCOUNT_ID = 1007)

Open in new window

0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Thank you Sean... works nice..

Thank you too lcohan...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.