Oracle SQL Pivot with Conditions convert rows to columns

I have the a table ITEM_SF with the following data

I want to convert the data into Columns with a few conditions:

Emplid      Account_nbr Item_Type_cd   Item_Amt
K-215200145 BOOKS001    C               600
K-215200145 BOOKS001    P               -600
K-215200145 EXP001      P               0
K-215200145 HLT001      C               100
K-215200145 HLT001      P               -100
K-215200145 REGFEE001   C               500
K-215200145 REGFEE001   P               -500
K-215200145 SSC001      C               350
K-215200145 SSC001      P               -350
K-215200145 TUT001      C               10200
K-215200145 TUT001      P               -5545.19
K-215200145 TUT001      W               -1566
K-215200145 VAT001      C               587.5
K-215200145 VAT001      P               -392.56
K-215200145 VAT001      W               -40.5
K-215200211 BOOKS001    C               600
K-215200211 HLT001      C               100
K-215200211 REGFEE001   C               500
K-215200211 SSC001      C               350
K-215200211 TUT001      C               16800
K-215200211 VAT001      C               917.5
K-215200602 BOOKS001    C               900
K-215200602 BOOKS001    P               -150
K-215200602 HLT001      C               100
K-215200602 REGFEE001   C               500
K-215200602 REGFEE001   P               0
K-215200602 SSC001      C               350
K-215200602 SSC001      P               0
K-215200602 TUT001      C               15600
K-215200602 TUT001      W               -2340
K-215200602 VAT001      C               872.5
K-215200602 VAT001      P               -7.5
K-215200602 VAT001      W               -117

My desired result is to show Charges, Discounts , Amount Paid and Tax Charges include (BookFees, HealthFees,RegistrationFees,SSCFees,TuitionFees,GeneralFees)

Emplid      BookFees    HealthFees  RegistrationFees    SSCFees TuitionFees GeneralFees Discount    Fees Paid   VAT
K-215200145 600         100         500                 350     10200           0               1606.6      7848.7      587.5
K-215200211 600         100         500                 350     16800           0               0           0           917.5       
K-215200602 900         100         500                 350     15600           0               2457        157.5       872.5

Charges

Where BookFees is where ACCOUNT_NBR =BOOKS001 
Health Fees is 100, SSCFees is 350 and RegistrationFees is 500. Tuition Fees is ITEM_AMT where ACCOUNT_NBR= TUT001, GeneralFees is ITEM_AMT where ACCOUNT_NBR=TUTPC001 and Item_type_cd = C (Charge)

, Discount is Sum of ITEM_AMT where ITEM_TYPE_CD ='W' FeesPaid is Sum of ITEM_AMT where ITEM_TYPE_CD ='P' VAT is Sum of ITEM_AMT where ACCOUNT_NBR=VAT001 and ITEM_TYPE_CD ='C' (Aditionally VAT should always be Charges*0.05)

I tried using Pivot but could not get all the columns mentioned

Answer

You can use conditional aggregation, which means using a case expression to define which values should be summed up in each column:

select empid,
       sum(case when Account_nbr = 'BOOKS001' then item_amt else 0 end) as book_fees,
       sum(case when Account_nbr = 'HLT001' then item_amt else 0 end) as health_fees,
       sum(case when Account_nbr = 'REGFEE001' then item_amt else 0 end) as registration_fees,
       . . . -- for the remaining fees
from item_sf
group by empid;

Advertisement

Answer