TSQL Pivot Table

Asked Feb 12 '18 at 13:00

Active Feb 12 '18 at 13:06

Viewed 43 times

I have query which i am using to fetch information. The query basically gets the worker name,ref, and pay, and earnings. I want to display all the information by pivoting the value of pay and earnings by the tax weeks. The code i am using at the moment is following:

SELECT *, 
        SUM(TOTAL_PAY+Holiday_pay) OVER (PARTITION BY personnel_ref, tax_period, tax_year) as [Total_Earning]
        into #tem
FROM(
SELECT  DISTINCT 
        Personnel_ref,
        Tax_period,
        Tax_Year,
        FirstName,
        LastName,
        Contract_Type,
        SUM(TOTAL_PAY) OVER (PARTITION BY PERSONNEL_REF,TAX_PERIOD,TAX_YEAR) AS [Total_Pay],
        (Holiday_Pay)
FROM #worker_totals w
)D

ORDER BY Tax_period ASC

The tax_period/week is dynamic value and is not hard coded value. I want the desired results to be as following:

                                                     |Tax_Period| 45        | 45           |  46        | 46            |  
personnel_ref | Firstname | Lastname | Contract_type | Tax_year | Total_Pay | Total_Earning | Total_Pay | Total_Earning |

edited Feb 12 '18 at 13:06

marc_s

732,580
175
1,330
1,459

asked Feb 12 '18 at 13:00

Shahbaz Hussain

2

can you pls add ddl and insert script for few rows and expected output. – Pawan Kumar Feb 12 '18 at 13:27
Better way of pivoting is to put it in SSRS and use the matrix table. – Shahbaz Hussain Feb 26 '18 at 12:21

TSQL Pivot Table

0 Answers0