Click here to Skip to main content
14,691,952 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I was trying to create pivot table from my query when i choose select * from it works fine but when i tries to select columns as shown in below code it gives me error Incorrect column name


select sr,PayDescr,PayMonAmt 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 mn
outer apply(
        select Descr,PayCat from maPayCat pc
        where mn.PayCat = pc.PayCat
        )Ctname
where  EmpID = mn.EmpID group by Ctname.Descr, Ctname.PayCat)   pv 
 pivot
 (
 sum (PayMonAmt) for PayDescr IN ([Basic Salary],[Food Allowance],[Tuition 
 Allowance],[Transportation Allowance])
 ) AS e 

Actually i am trying create pivot for below code
 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 mn 
outer apply( select Descr,PayCat 
from maPayCat pc where mn.PayCat = pc.PayCat )Ctname
 where EmpID = mn.EmpID
 group by Ctname.Descr, Ctname.PayCat


What I have tried:

I worsk fine for Select * statement but not when specify column names
Posted
Updated 27-Mar-20 4:11am
Comments
ZurdoDev 27-Mar-20 8:46am
   
What is the exact error?
$ultaNn 27-Mar-20 9:02am
   
Invalid column name 'PayDescr'.
Invalid column name 'PayMonAmt '.

1 solution

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
    
    -- TODO: This filter seems to be redundant?
    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.
   
Comments
Maciej Los 27-Mar-20 9:27am
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900