SQL Server : dynamic pivot over many columns-CodePudding

I have a table that looks like this:

DECLARE @Temp TABLE (
 fldID bigint
,fldYear bigint
,fldMonth bigint
,fldMonthName nvarchar(350)
,fldPayrollId bigint
,fldFullName nvarchar(350)
,fldDailyWage float
,fldMonthlyWage  float
,fldkarkardDay  float
,fldPriceMaskan  float
,fldPriceChild float
,fldPriceFood  float
,fldAllAdditions  float
,fldPriceTax  float
,fldPriceSanavat  float
,fldPriceEzafeKari  float
,fldPriceEidiPadash  float
,fldEmployeeShare  float
,fldEmployerShare  float
,fldUnemploymentShare  float
,fldSumEmployer  float);

like so

insert @Temp(fldID, fldYear, fldMonth, fldMonthName, fldPayrollId, fldFullName, fldDailyWage, fldMonthlyWage , fldkarkardDay , fldPriceMaskan , fldPriceChild, fldPriceFood , fldAllAdditions , fldPriceTax , 
fldPriceSanavat, fldPriceEzafeKari , fldPriceEidiPadash , fldEmployeeShare , fldEmployerShare , fldUnemploymentShare , fldSumEmployer  )
values(1, 1400, 2, N'February', 212, N'500000', N'6500', N'29', N'52000', N'98750', N'1900000', N'900000', N'30000', N'900000', N'60000', N'650000', N'0', N'0', N'1900000', N'256000', N'256000'),(2, 1399, 3, N'March'   , 214, N'5200000', N'0', N'30', N'65900', N'6520', N'1900000', N'1000', N'98500', N'1900000', N'1900000', N'1900000', N'0', N'1900000', N'0', N'256000', N'0'),(3, 1400, 4, N'April'   , 216, N'62200000', N'35600', N'30', N'87900', N'65000', N'0', N'6520', N'1900000', N'1900000', N'0', N'1900000', N'1900000', N'1900000', N'1900000', N'256000', N'25600'),(4, 1399, 5, N'May'       , 218, N'522000', N'85000', N'2800', N'65400', N'52200', N'0', N'14780', N'1900000', N'1900000', N'0', N'1900000', N'0', N'1900000', N'1900000', N'256000', N'0')

I'm trying the pivot it as follows:

enter image description here

My code is as follows but the pivot command cannot identify the code and the correct output

SELECT 
     N'parameter' parameter
    ,p.fldMonthName 
    ,p.fldCols
FROM 
(
    SELECT 
        N'[fldDailyWage],[fldMonthlyWage],[fldkarkardDay],[fldPriceMaskan],[fldPriceChild],[fldPriceFood],[fldAllAdditions],[fldPriceTax]' fldCols
        ,* 
    FROM @Temp where fldYear = 1399
) x
PIVOT  
(
    count(fldID)
    FOR fldMonthName IN (fldCols)
) p

CodePudding user response：

I would use unpivot with grouping by column parameter as follows:

Select u.parameter, Sum(Case When fldMonth=2 Then u.value End) As 'February',
                    Sum(Case When fldMonth=5 Then u.value End) As 'May'
From
(
select fldMonth, [fldDailyWage],[fldMonthlyWage],[fldkarkardDay],[fldPriceMaskan],[fldPriceChild],[fldPriceFood],[fldAllAdditions],[fldPriceTax]
from @Temp) t
unpivot
( 
  value
  for parameter in ([fldDailyWage],[fldMonthlyWage],[fldkarkardDay],[fldPriceMaskan],[fldPriceChild],[fldPriceFood],[fldAllAdditions],[fldPriceTax])
) u
Group by u.parameter