CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

source pivot does not like column names

I have a query below. However in the last select two fields, USDVal & cp are invalid column names when I run the query. I do not understand why though?

Because if I run the query below excluding the last line ("as source..") the query runs.

;with s as
(
   select cp, mktVal, date 
   from tblDaily
   where date = '2017-01-30' and id = 'SFAB'
), h as 
(
    select idMK, name, cp, wgt 
    from tblDrift 
    where date = '2017-03-30' and idSub = 'V12' and wgt <> 0
), m as
(
   select h.idMK, h.name, h.wgt * s.mktVal  as USDVal, h.cp
   from h left join s on h.cp = s.cp
)   
select idMK, name, USDVal, cp from m 
as source pivot(max(USDVal) for cp in ([BPP],[NCV])) as pvt

Upvotes: 0

Views: 37

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try to change your last two lines to something like this:

SELECT pvt.*
FROM
(
    select idMK, name, USDVal, cp from m 
) as source 
pivot(max(USDVal) for cp in ([BPP],[NCV])) as pvt

Upvotes: 1

Related Questions