Incorrect column for pivot table

Question

0.00/5 (No votes)

See more:

, +

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 27-Mar-20 2:43am

$ultaNn

Updated 27-Mar-20 3:11am

Add a Solution

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

Add a Solution

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

Richard Deeming · Accepted Answer · 2020-03-27T03:12:00

As the error says, the column names in your outer SELECT are incorrect.

Try:

SQL

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.

Incorrect column for pivot table

1 solution

Solution 1

Add your solution here

Preview 0

Incorrect column for pivot table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us