SQL Server pair column pivot

Question

I am using SQL server 2014. I required the following report. HeadName is dynamic that can be any name if HeadType is earning then show Tot_hrs and Amount, if HeadType is the deduction, then show the only Amount.

Note: make sure that HeadName Data should not be hardcoded in the query because the user can add data in earning and deduction for .eg holiday work OT, incentive, Late deduction, Loan etc

I will provide two i.e fromPayslipDate and ToPayslipDate result should be displayed as the picture below.

CREATE TABLE #PaySlip (PaySlipID int, SlipNo varchar(50), SlipDate date, EmployeeID int, RatePerHrs numeric(18,2));
CREATE TABLE #PaySlipD (PaySlipID int,HeadType varchar(50),OrderNo int, HeadName varchar(50), Tot_hrs numeric(18,2), Amount numeric(18,2));


INSERT INTO #PaySlip
    (PaySlipID,SlipNo,SlipDate ,EmployeeID,RatePerHrs)
    values
    (3, '2018-0001',    '2018-10-03',   30, 7.00),
    (4, '2018-0002',    '2018-10-03',   23, 8.00);

INSERT INTO #PaySlipD
    (PaySlipID,HeadType,OrderNo,HeadName,Tot_hrs,Amount)
    values
(   3   ,   'Earning'   ,   2   ,   'Normal Hours'  ,   82  ,   574 ),
(   3   ,   'Earning'   ,   2   ,   'OT Normal'     ,   13.57   ,   0   ),
(   3   ,   'Earning'   ,   3   ,   'OT Holiday'    ,   0   ,   0   ),
(   3   ,   'Earning'   ,   5   ,   'Incentive'     ,   0   ,   0   ),
(   3   ,   'Deduction' ,   1   ,   'Loan'          ,   0   ,   0   ),
(   3   ,   'Deduction' ,   2   ,   'Tax'           ,   0   ,   26.13   ),
(   3   ,   'Deduction' ,   3   ,   'Employee NPF'  ,   0   ,   34.44   ),
(   3   ,   'Deduction' ,   4   ,   'Employer NPF'  ,   0   ,   48.22   ),
(   4   ,   'Earning'   ,   1   ,   'Normal Hours'  ,   71.24   ,   498.68  ),
(   4   ,   'Earning'   ,   2   ,   'OT Normal'     ,   4.85    ,   0   ),
(   4   ,   'Earning'   ,   3   ,   'OT Holiday'    ,   0   ,   0   ),
(   4   ,   'Earning'   ,   5   ,   'Incentive'     ,   0   ,   0   ),
(   4   ,   'Deduction' ,   1   ,   'Loan'          ,   0   ,   0   ),
(   4   ,   'Deduction' ,   2   ,   'Tax'           ,   0   ,   15.25   ),
(   4   ,   'Deduction' ,   3   ,   'Employee NPF'  ,   0   ,   29.92   ),
(   4   ,   'Deduction' ,   4   ,   'Employer NPF'  ,   0   ,   41.89   )
drop table #PaySlip
drop table #PaySlipD

Zeki Gumus · Accepted Answer · 2018-11-15T11:55:27.280

I assume HeadNames for 'Earning' HeadType is static. If yes this following script can help you. (I also put EmployeeId to cover more than one records)

drop table #PaySlip;
drop table #PaySlipD;
CREATE TABLE #PaySlip (PaySlipID int, SlipNo varchar(50), SlipDate date, EmployeeID int, RatePerHrs numeric(18,2));
CREATE TABLE #PaySlipD (PaySlipID int,HeadType varchar(50),OrderNo int, HeadName varchar(50), Tot_hrs numeric(18,2), Amount numeric(18,2));


INSERT INTO #PaySlip
    (PaySlipID,SlipNo,SlipDate ,EmployeeID,RatePerHrs)
    values
    (3, '2018-0001',    '2018-10-03',   30, 7.00),
    (4, '2018-0002',    '2018-10-03',   23, 8.00);

INSERT INTO #PaySlipD
    (PaySlipID,HeadType,OrderNo,HeadName,Tot_hrs,Amount)
    values
(   3   ,   'Earning'   ,   2   ,   'Normal Hours'  ,   82  ,   574 ),
(   3   ,   'Earning'   ,   2   ,   'OT Normal'     ,   13.57   ,   0   ),
(   3   ,   'Earning'   ,   3   ,   'OT Holiday'    ,   0   ,   0   ),
(   3   ,   'Earning'   ,   5   ,   'Incentive'     ,   0   ,   0   ),
(   3   ,   'Deduction' ,   1   ,   'Loan'          ,   0   ,   0   ),
(   3   ,   'Deduction' ,   2   ,   'Tax'           ,   0   ,   26.13   ),
(   3   ,   'Deduction' ,   3   ,   'Employee NPF'  ,   0   ,   34.44   ),
(   3   ,   'Deduction' ,   4   ,   'Employer NPF'  ,   0   ,   48.22   ),
(   4   ,   'Earning'   ,   1   ,   'Normal Hours'  ,   71.24   ,   498.68  ),
(   4   ,   'Earning'   ,   2   ,   'OT Normal'     ,   4.85    ,   0   ),
(   4   ,   'Earning'   ,   3   ,   'OT Holiday'    ,   0   ,   0   ),
(   4   ,   'Earning'   ,   5   ,   'Incentive'     ,   0   ,   0   ),
(   4   ,   'Deduction' ,   1   ,   'Loan'          ,   0   ,   0   ),
(   4   ,   'Deduction' ,   2   ,   'Tax'           ,   0   ,   15.25   ),
(   4   ,   'Deduction' ,   3   ,   'Employee NPF'  ,   0   ,   29.92   ),
(   4   ,   'Deduction' ,   4   ,   'Employer NPF'  ,   0   ,   41.89   )

    DECLARE @ColumnsEarning     VARCHAR(MAX);
    DECLARE @ColumnsEarningWithHoursAlias       VARCHAR(MAX);
    DECLARE @ColumnsEarningWithAmountAlias      VARCHAR(MAX);
    DECLARE @ColumnsDeduction    VARCHAR(MAX);
    DECLARE @SQLString  VARCHAR(MAX);

    SET @ColumnsEarning=STUFF((SELECT distinct ',' + QUOTENAME(HeadName) 
                    from #PaySlipD  WHERE HeadType='Earning'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

    --I put alias to understand which one related Hours which one Amount
        SET @ColumnsEarningWithHoursAlias=STUFF((SELECT distinct ',' + QUOTENAME(HeadName) +' '+QUOTENAME(HeadName+'_Hours')
                        from #PaySlipD  WHERE HeadType='Earning'
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'');

        SET @ColumnsEarningWithAmountAlias=REPLACE(@ColumnsEarningWithHoursAlias,'_Hours','_Amount')

    SET @ColumnsDeduction=STUFF((SELECT distinct ',' + QUOTENAME(HeadName) 
                    from #PaySlipD  WHERE HeadType='Deduction'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');
PRINT(@ColumnsEarning)
PRINT(@ColumnsEarningWithHoursAlias)
PRINT(@ColumnsEarningWithAmountAlias)


SET @SQLString='
SELECT * FROM #PaySlip P
LEFT JOIN 
    (
    SELECT PaySlipId,'+@ColumnsEarningWithHoursAlias+' FROM 
        ( SELECT T.PaySlipId,HeadName,Tot_hrs FROM #PaySlipD T
        WHERE HeadType=''Earning''
        ) S
        PIVOT
        (
          sum(Tot_hrs)
          for HeadName in ('+@ColumnsEarning+')
        ) AS EH
    ) AS EH ON P.PaySlipId=EH.PaySlipId
LEFT JOIN 
    (
    SELECT PaySlipId,'+@ColumnsEarningWithAmountAlias+' FROM 
        ( SELECT T.PaySlipId,HeadName,Amount FROM #PaySlipD T
        WHERE HeadType=''Earning''
        ) S
        PIVOT
        (
          sum(Amount)
          for HeadName in ('+@ColumnsEarning+')
        ) AS EA
    ) AS EA ON EH.PaySlipId = EA.PaySlipId
LEFT JOIN 
    (
    SELECT * FROM 
        ( SELECT T.PaySlipId,HeadName,Amount FROM #PaySlipD T
        WHERE HeadType=''Deduction''
        ) S
        PIVOT
        (
          sum(Amount)
          for HeadName in ('+@ColumnsDeduction+')
        ) AS D
    ) AS D ON EH.PaySlipId = D.PaySlipId

    ';

EXEC(@SQLString)

thanks for the reply but HeadName is dynamic user can add, modify & delete the data, so we can't hardcoded the Name. for e.g, if I add in earning Holiday work then it is not showing that data — Naweez, Nov 15 '18 at 05:44
Hi @naweez, I have updated the script. Now it is fully dynamic. I also put Alias to Earning columns to understand which one related with Hours, which one Amount. — Zeki Gumus, Nov 15 '18 at 08:54
thanks for the reply. now I modified actual data, please read it again. — Naweez, Nov 15 '18 at 11:20
Hi @Zeki Gumus, it's working fi9 only problem coming when adding some head name so it not showing sequentially. if i want filter employee ID and Payslip date range where we can e.g employee = 1 and Slipdate between 2018-01-01 and 2018-01-31. — Naweez, Nov 19 '18 at 03:25

SQL Server pair column pivot

1 Answers1