CODEWITHSUNDEEP
sqlreport
Sean Whiting
Sean Whiting

Reputation: 47

Oracle SQL - Present row data in columns

I'm trying to produce a report using Oracle SQL that is more user friendly. The data will be exported to Excel for the user.

I have an example table

ID CUSTOMER_ID AWARD AMOUNT NET_AMOUNT
1 1001 AWARD1 500 300
2 1001 AWARD3 600 250
3 1001 AWARD5 400 400
4 1002 AWARD1 500 500
5 1002 AWARD2 300 300
6 1002 AWARD3 300 300
7 1003 AWARD4 200 200
8 1004 AWARD3 300 300
9 1004 AWARD4 200 200
10 1004 AWARD5 400 400

What I am trying to achieve is Oracle SQL that will produce an output something along the lines of:

RESULTS:

 1. 1001|AWARD1;500;300;NULL;NULL;NULL;AWARD3;600;250;NULL;NULL;NULL;AWARD5;400;400
 2. 1002|AWARD1;500;300;AWARD2;300;300;AWARD3;600;250;NULL;NULL;NULL;NULL;NULL;NULL
 3. 1003|NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;AWARD4;200;200;NULL;NULL;NULL
 4. 1004|NULL;NULL;NULL;NULL;NULL;NULL;AWARD3;600;250;AWARD4;200;200;AWARD5;400;400

I have previously used:

 LISTAGG (AWARD|| ';' || AMOUNT || ';' || NET_AMOUNT||';')
         WITHIN GROUP (ORDER BY CUSTOMER_ID)
            AS EXAMPLE_OUTPUT

to list all the awards on a single row against the CUSTOMER_ID.

However this option data doesn't line up neatly enough (in Excel) as each customer has a different set of awards.

Upvotes: 0

Views: 39

Answers (1)

RohithPg
RohithPg

Reputation: 121

If you are sure that only AWARD 1 through 5 will be present , you can use the statement below which utilises oracle PIVOT and listagg. Please note that I have used a string 'NULL;NULL;NULL;' to represent an empty value which you will have to remove from your excel .

select customer_id, (NVL((LISTAGG (award1,';')
        WITHIN GROUP (ORDER BY customer_id)),'NULL;NULL;NULL;') ||';'||
                    NVL((LISTAGG (award2,';')
        WITHIN GROUP (ORDER BY customer_id)),'NULL;NULL;NULL;')||';'||
        NVL((LISTAGG (award3,';')
        WITHIN GROUP (ORDER BY customer_id)),'NULL;NULL;NULL;') ||';'||
        NVL((LISTAGG (award4,';')
        WITHIN GROUP (ORDER BY customer_id)),'NULL;NULL;NULL;')||';'||
        NVL((LISTAGG (award5,';')
        WITHIN GROUP (ORDER BY customer_id)),'NULL;NULL;NULL;')||';')
                    as output
           from
(select * from customer c
pivot(
min(award||';'||amount||';'||net_amount)
  for award in('AWARD1' as AWARD1,'AWARD2' as AWARD2,'AWARD3' as AWARD3,'AWARD4' as AWARD4,'AWARD5' as AWARD5)
) order by customer_id)
 group by customer_id;

sqlfiddle link http://sqlfiddle.com/#!4/94fef2/113

Upvotes: 0

Related Questions