As the error says, the column names in your outer
SELECT
are incorrect.
Try:
SELECT
sr,
[Basic Salary],
[Food Allowance],
[Tuition Allowance],
[Transportation Allowance]
FROM
(
SELECT
sr = CASE
when rtrim(Ctname.PayCat)='GS' then 1
when rtrim(Ctname.PayCat)='BS' then 2
when rtrim(Ctname.PayCat)='HA' then 3
when rtrim(Ctname.PayCat)='TA' then 4
when rtrim(Ctname.PayCat)='FA' then 5
else 6
END,
PayDescr = rtrim(Ctname.Descr),
SUM(PayMonAmt) as PayMonAmt
FROM
maEmpPay As mn
OUTER APPLY
(
SELECT Descr, PayCat
FROM maPayCat pc
WHERE mn.PayCat = pc.PayCat
) As Ctname
WHERE
EmpID = mn.EmpID
GROUP BY
Ctname.Descr,
Ctname.PayCat
)
PIVOT
(
SUM(PayMonAmt) FOR PayDescr IN
(
[Basic Salary],
[Food Allowance],
[Tuition Allowance],
[Transportation Allowance]
)
) As e
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[
^]
NB: The
WHERE
clause in your inner
SELECT
doesn't seem to serve any purpose. The only possible source of the
EmpID
column is the
maEmpPay
table, so your filter is looking for records where
maEmpPay.EmpID
is equal to itself. That will be true for all records, unless you have
Null
values in that column.