I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're tables differently.
This is the select at the bottom of my stored procedure:
select
(CASE WHEN [Fitter] IS NULL THEN (Select Distinct substring([First Name],1,1)+' '+[Second Name] from Fitters where [Fitter Id]=FitterId) ELSE Fitter END) AS Fitter,
sum([Install Sell]) as [Install Sell],
sum([Install Cost]) as [Install Cost],
sum([Install Cost Amt]) as Gross,
(select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as CIS,
(select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as VAT,
sum([Install Cost Amt]) - (select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as FittersPay,
sum([Install Cost Amt]) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as Datafile
from @TempTable
group by FitterId, Fitter, TaxStatus, VatStatus
This is the data it returns:
I would like for the columns to pivot as rows and the rows to pivot as columns...
I have searched around online and am struggling to figure this out, i was wondering if i could get some assistance with this please.
Any assistance is appreciated.
This is done by first unpivoting and then pivoting. Here is an example, that you can adjust yo your data:
Output1:
Output2:
It depends on type of your data, but you could need to do it dynamically. There is plenty of examples on the site. Just search for
dynamic pivoting
.EDIT:
Something like this: