Can I use Select Query In Pivot For Function

See more:

SQL-Server-2008R2

select * from (
SELECT SaleRetrn_Date,Party_Idno,Party_Name,Vch_No,TIN_No,TotAmount,(TotAmount-VAT_CST_TotAmnt)AS Amnt,VAT_CST_TotAmnt as CST_Amnt,Tax_Amnt,Tax_Cat
FROM (
SELECT CONVERT(DATETIME,MH.MtrlRcptToHO_Date,105) AS SaleRetrn_Date, MH.Store_Idno, LM.LocationUniq_Name,
CM.CompanyMaster_Idno AS Party_Idno,CM.Company_Name AS Party_Name,
CONVERT(NVARCHAR,MH.MtrlRcptToHO_No) AS Vch_No,
CONVERT(BIGINT,ISNULL(CM.Tin_No,0))AS TIN_No,
ISNULL(MH.TotNet_Amount,0) AS TotAmount,
ISNULL(MH.VAT_CST_TotAmnt,0) as VAT_CST_TotAmnt,
ISNULL(SUM(MD.Amount-MD.Tax_Amnt),0) as Tax_Amnt,
MD.Tax_Cat
FROM tblMtrlRcptToHOHead MH
INNER JOIN tblLocationMast LM ON MH.Store_Idno = LM.Location_Idno
LEFT OUTER JOIN tblCompanyMaster CM ON LM.Comp_Idno = CM.CompanyMaster_Idno
inner join tblMtrlRcptToHODetl MD on MH.MtrlRcptToHO_Idno=MD.MtrlRcptToHOHead_Idno
WHERE MH.MtrlRetSTO_Type=2 and MH.Year_Idno= 2
GROUP BY MH.MtrlRcptToHO_Date,MH.Store_Idno,LM.LocationUniq_Name,CM.CompanyMaster_Idno,CM.Company_Name,MH.MtrlRcptToHO_No,CM.Tin_No,MH.TotNet_Amount,
MH.VAT_CST_TotAmnt,MD.Tax_Cat
)A
)B
pivot
(
max(Tax_Amnt)
For Tax_Cat in (select distinct Category_Name from tblTaxMaster)
)p