title: SQL Server : incorrect syntax near '('. Expecting ID, QUOTED_ID Error with SQL Pivot
body: <p>I have used CTE and getting compilation error for last CTE. long time search google but still not understand where I made the mistake in code. so please some one tell me what to change in code to fix the error message</p>
  <blockquote>
  <p>Incorrect syntax near '('. Expecting ID, QUOTED_ID</p>
  </blockquote>
  <p>This CTE is throwing the error and this is last CTE in my code.</p>
  <pre><code>,Cte2 AS
  (
      SELECT *
      FROM
          (SELECT 
               EarningID, Section, LineItem, DisplayInCSM, Type, 
               Broker, ItemValue, Period, hierarchy
           FROM
               Cte1) t
      PIVOT
          (MAX(ItemValue)
               FOR Broker IN ([5W],[8K],[CL],[DA],[EQ],[FA],[GS],[HM],[HQ],[JY],[KW],[ML],[MS],[MV],[SL],[UA],[WB])
          ) AS P
  )
  </code></pre>
  <p>My full code</p>
  <pre><code>DECLARE @Columns as VARCHAR(MAX)
  DECLARE @Ticker VARCHAR(20), 
  @TickerID VARCHAR(20),
  @ClientCode VARCHAR(20)
  
  DECLARE @CSM_ID INT
  SET @TickerID='ADS'
  SET @ClientCode='ADS'
  
  IF OBJECT_ID(N'tempdb..#Brokers') IS NOT NULL
  BEGIN
      DROP TABLE #Brokers
  END
  
  SELECT DISTINCT @Ticker=Ticker FROM tblTickerMasterId WHERE MasterId=@TickerID
  SELECT TOP 1 @CSM_ID=CSM_ID FROM tblCSM_Tuner_Client WHERE TickerID=@TickerID
  
  SELECT * Into #Brokers FROM
  (
      Select A.BrokerCode, B.BrokerName                                                  
      From tblClientBroker_Earnings A                                                  
      Join tblBroker B ON SUBSTRING(A.BrokerCode,1,len(A.BrokerCode)-charindex('-',A.BrokerCode))=B.Brokercode                                                       
      Where A.ClientCode=@ClientCode And A.Ticker=@Ticker                                          
      /*AND A.BrokerCode IN (SELECT [DATA] FROM SplitStringToTable(@SelectedBrokers,',') WHERE TRIM([DATA])&lt;&gt;'')*/
  ) x
  
  SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(BrokerCode)
  FROM
  (
      SELECT DISTINCT BrokerCode
      FROM #Brokers where TRIM(BrokerCode) &lt;&gt; ''
  ) AS B
  ORDER BY B.BrokerCode
  
  ;WITH DirectReports as 
  (
      SELECT      CSM_ID, 
                  ID, 
                  ParentID, 
                  DisplayInCSM, 
                  Type,
                  FontName,
                  FontStyle,
                  FontSize,
                  UnderLine,
                  BGColor,
                  LineItemID,
                  Presentation,
                  BrokerOrientation,
                  AnnualFormat,
                  CalculationMethod,
                  Indent,
                  FGColor,
                  Box,
                  HeadingSubHeading,
                  ColOrder,
                  @TickerID AS TickerID,
                  hierarchy = FORMAT(ID,'0000'), 
                  level = 0
  
      FROM        tblCSM_ModelDetails 
      WHERE       ISNULL(ParentID, 0) = 0
                  AND Type&lt;&gt;'BM'
                  AND CSM_ID=@CSM_ID
  
      UNION ALL
      SELECT      e.CSM_ID, 
                  e.ID, 
                  e.ParentID, 
                  e.DisplayInCSM, 
                  e.Type,
                  e.FontName,
                  e.FontStyle,
                  e.FontSize,
                  e.UnderLine,
                  e.BGColor,
                  e.LineItemID,
                  e.Presentation,
                  e.BrokerOrientation,
                  e.AnnualFormat,
                  e.CalculationMethod,
                  e.Indent,
                  e.FGColor,
                  e.Box,
                  e.HeadingSubHeading,
                  e.ColOrder,
                  @TickerID AS TickerID,
                  hierarchy = d.hierarchy + '.' + FORMAT(e.id,'0000'), 
                  level = level + 1
  
      FROM        tblCSM_ModelDetails e
      JOIN        DirectReports d on e.ParentID = d.ID
      WHERE       e.Type&lt;&gt;'BM'
                  AND e.CSM_ID=@CSM_ID
  )
  
  ,Cte1 as 
  (
      SELECT AA.EarningID,AA.Section,AA.LineItem,AA.Ticker, r.DisplayInCSM, r.Type,r.hierarchy, AA.Broker, AA.ItemValue, AA.Period,r.ColOrder
      FROM DirectReports r 
      LEFT OUTER JOIN
      (                                                  
          Select b.*,L.ID AS LineItemID,L.TickerID                                                     
          From tblOutputDetl_CSMTuner b                                                  
          INNER JOIN TblLineItemTemplate L 
          ON b.LineItem= L.LineItem 
          WHERE b.Ticker=@Ticker AND L.TickerID=@TickerID                                    
      ) AA
      ON (AA.LineItemID=r.LineItemID 
  
  )
  ,Cte2 as 
  (
    SELECT *
    FROM
      (
          SELECT EarningID,Section,LineItem, DisplayInCSM, Type, Broker, ItemValue, Period,hierarchy
          from Cte1
      ) t
      PIVOT
      (
          MAX(ItemValue)
          FOR Broker IN ([5W],[8K],[CL],[DA],[EQ],[FA],[GS],[HM],[HQ],[JY],[KW],[ML],[MS],[MV],[SL],[UA],[WB])
      ) AS P
  )
  
  SELECT * FROM Cte2 Order BY hierarchy
  
  IF OBJECT_ID(N'tempdb..#Brokers') IS NOT NULL
  BEGIN
      DROP TABLE #Brokers
  END
  </code></pre>