[askdba] Re: Query format - Urgent

  • From: "Subodh Deshpande" <subodh_deshpande@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx, "LazyDBA.com Discussion" <oracledba@xxxxxxxxxxx>
  • Date: Tue, 12 Oct 2004 00:18:22 -0500

hi pradhan,

SQL> desc pradhan
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNUM                                   FLOAT(*)
 SALDESC                                  VARCHAR2(10)
 AMOUNT1                                  FLOAT(*)
 AMOUNT2                                  FLOAT(*)

SQL> select * from pradhan;

    EMPNUM SALDESC       AMOUNT1    AMOUNT2
---------- ---------- ---------- ----------
         1 basic          100.32          0
         1 splall            132         50
         1 da                200         10
         1 inctax             50          0

  1  select empnum, sum(decode(saldesc,'basic',amount1)) basicamt1,
  2         sum(decode(saldesc,'basic',amount2)) basicamt2,
  3         sum(decode(saldesc,'splall',amount1)) spl1,
  4         sum(decode(saldesc,'splall',amount2)) spl2,
  5         sum(decode(saldesc,'da',amount1)) da1,
  6         sum(decode(saldesc,'da',amount2)) da2,
  7         sum(decode(saldesc,'inctax',amount1)) inctax1,
  8         sum(decode(saldesc,'inctax',amount2)) inctax2
  9  from pradhan
 10* group by empnum
SQL> /

    EMPNUM  BASICAMT1  BASICAMT2       SPL1       SPL2        DA1        DA2    
INCTAX1    INCTAX2
---------- ---------- ---------- ---------- ---------- ---------- ---------- 
---------- ----------
         1     100.32          0        132         50        200         10    
     50          0


----- Original Message -----
From: "Pradhan" <pradhan@xxxxxxxxxxxxxxxx>
Date: Mon, 11 Oct 2004 11:12:29 +0530
To: "LazyDBA.com Discussion" <oracledba@xxxxxxxxxxx>
Subject: [askdba] Query format - Urgent

> Hi Folks,
> I have a query which gives me data in vertical format eg:
> 
> select empnum, saldesc, amount1, amount2 from salinfo
> 
> empnum    saldesc    amount1    amount2
> --------    -------    -------    ---------
> 001          basic        100.00        0.00
> 001          splall         132.00      50.00
> 001          da            200.00       10.00
> 001          inctax       50.00          0.00
> 
> 
> I want the same to be formatted as
> 
> empnum   basicamt1   basicamt2   splallamt1    splallamt2    daamt1    daamt2 
>    inctaxamt1    inctaxamt2
> --------  ----------    --------    ---------    ----------    -------    
> ------    -----------    ----------
> 001         100.00       0.00            132.00        50.00        200.00    
>   10.00      50.00            0.00
> 
> 
> can any one help me in getting the data in the above said format.
> 
> Warm Regards,
> 
> Pradhan
> 
> 



Good Luck..Subodh Deshpande

===============================================
Experience Is Knowledge
Wisdom Is Philosophy
==============================================







-- 
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10


Other related posts: