Error in PIVOT using CTE in VIEW

  • Hi,

    Can anyone tell me what's wrong with this code ...

    I would like to have rows presented as columns. That's why I use the PIVOT function at the end.

    The resultset will be presented in Excel using an external connection to the view.

    When I try to save the view I get the error

    Msg 4104, Level 16, State 1, Procedure _TEST, Line 47

    The multi-part identifier "vk.OppCode" could not be bound.

    Code (restricting the columns that I actually have to the relevant columns only):

    USE [DBTest]

    GO

    /****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [dbo].[_TEST]

    AS

    WITH Query1 AS

    (

    SELECTo.Code AS OppCode

    FROMOpportunities o

    ),

    Query2 AS

    (

    SELECTa.ProjectNumber AS ProjNo,

    SUM(a.ItemCount) AS QtyHours,

    SUM(a.AmountActual) AS AmtActual

    FROMAbsences a

    CROSS APPLY (SELECT * FROM [dbo].[AllDays] (a.StartDate, a.EndDate)) t

    WHEREa.Type = 3113

    AND a.Status NOT IN (2, 5)

    GROUP BY

    a.ProjectNumber

    ),

    Query3 AS

    (

    SELECTa.OpportunityID,

    o.Code,

    a.ProjectNumber,

    a.Startdate,

    MONTH(a.StartDate) AS MonthStart,

    a.FreeNumberField_01 AS Amt

    FROMAbsences a

    LEFT OUTER JOIN Opportunities o ON a.OpportunityID = o.ID

    WHEREa.Type = 3132

    AND a.Status NOT IN (2, 5)

    )

    SELECTvk.OppCode,

    [1] AS Januari,

    [2] AS Februari,

    [3] AS Maart,

    [4] AS April,

    [5] AS Mei,

    [6] AS Juni,

    [7] AS juli,

    [8] AS Augustus,

    [9] AS September,

    [10] AS Oktober,

    [11] AS November,

    [12] AS December

    FROMQuery1 vk

    LEFT OUTER JOIN Query2 ur ON vk.OppCode = ur.ProjNo

    LEFT OUTER JOIN Query3 mv ON vk.OppCode = mv.Code

    PIVOT (SUM(mv.Amt) FOR mv.MonthStart IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS Months

    GO

    Thanks!

  • Your select list is actually pulling fields from the pivoted query, and you gave that query the alias "months", not vk.

    Try "months.oppcode" or just "oppcode". Or to make it clearer, select the fields you want in another CTE (the query and the joins), then do the pivot last

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply