Search code examples
sqldynamicpivotunpivot

I need to construct table like below, dynamically using pivot or unpivot

EmpSalary table:

EmpCode BASIC   HRA  CONV
--------------------------
   1    10000   500  300
   2    10000   500  300

Desired output:

   SalaryCode   SalaryDetails
    EmpCode           1
    BASIC           10000
    HRA               500
    CONV              300
   Total            10800

    EmpCode           2
    BASIC           10000
    HRA               500
    CONV              300
   Total            10800
Solution

  • Actually we need what DBMS are you using.

    The following code that includes unpivot clause works for Sql-Server or Oracle :

    select SalaryCode, SalaryDetails
  from   
   (select EmpCode, BASIC,HRA,CONV,
           (BASIC+HRA+CONV) sub_Total  
      from EmpSalary 
      ) p  
unpivot  
   (SalaryDetails for SalaryCode in
      (EmpCode,BASIC,HRA,CONV,sub_Total)  
) unpvt;

    Rextester Demo