title: SQL Pivot dynamic query error
body: <p>I tried to convert rows values into columns...Below is my query...</p>
  
  <pre><code>Select * from (SELECT  *
              FROM    (   SELECT PROJ.PROJ_ID,
                                 PROJ.PROJ_NM AS [Project Name],
                                 PROJ.PROJ_DS AS [Project Description],
                                 convert(varchar(10), PROJ.PROJ_ACTL_LNCH_DT, 110) [Actual Completed Date],
                                 PROJ.PROJ_SMRY_DS AS [Project Summary],
                                 dbo.udf_BankContacts(PROJ.PROJ_ID) AS [BankContact],  
                                 convert(varchar(10), PROJ.PROJ_EST_LNCH_DT, 110) AS [Estimated Launch Date],
                                 PROJ.ENTER_DT AS [Begin Date],
                                 PROJ_STA.PROJ_STA_DS AS [Project Status],
                                 SFTW_DEV_MTHD.SFTW_DEV_MTHD_NM AS [Software Development Method],
                                 PROJ_PHASE.PROJ_PHASE_DS AS [Phase],
                                 PROJ_CTGY.PROJ_CTGY_DS AS [Project Category],
                                 (CASE  WHEN PROJ.ARCH_IN='0' THEN 'N' ELSE 'C' END) AS [Archive],
                                 PROJ.PHASE_CMNT_TX AS [Phase Comment],
                                 PROD_TYPE_DS
  
                          FROM  dbo.Project PROJ  
                           left join dbo.PROJ_PROD  PP on PROJ.PROJ_ID = PP.PROJ_ID
                           left join dbo.PROD_TYPE PT on PP.PROD_TYPE_ID = PT.PROD_TYPE_ID
                           LEFT JOIN DBO.PROJ_STA ON PROJ.PROJ_STA_ID = PROJ_STA.PROJ_STA_ID
                           left join dbo.SFTW_DEV_MTHD on PROJ.SFTW_DEV_MTHD_ID = SFTW_DEV_MTHD.SFTW_DEV_MTHD_ID
                           left join dbo.PROJ_PHASE on PROJ.PROJ_PHASE_ID = PROJ_PHASE.PROJ_PHASE_ID
                           left join dbo.PROJ_CTGY on PROJ.PROJ_CTGY_ID = PROJ_CTGY.PROJ_CTGY_ID
                      ) data
  
                      PIVOT
                      (   MAX(PROD_TYPE_DS)
                          FOR PROD_TYPE_DS IN ([PT1],[PT2])
                      ) pvt2
  
                      where PROJ_ID is not null) AS  ProdType
  LEFT JOIN (SELECT  *
              FROM    
              (   
                select PROJ_ID,
                  PROJ_APRV_TYPE_DS = case 
                              when col ='PROJ_APRV_TYPE_DS' 
                              then PROJ_APRV_TYPE_DS 
                              else PROJ_APRV_TYPE_DS+col end,
                  value
                from
                (
                   SELECT PROJ.PROJ_ID,
  
                     PAT.PROJ_APRV_TYPE_DS PROJ_APRV_TYPE_DS,
                     convert(varchar(10), PATS.APRV_EXPT_BY_DT, 120) APRV_EXPT_BY_DT, 
                     convert(varchar(10), PATS.APRV_CMPL_DT, 120) APRV_CMPL_DT                   
                   FROM  dbo.Project PROJ  
                   left join [dbo].[PROJ_APRV_TYPE_STA] PATS 
                      on PROJ.PROJ_ID = PATS.PROJ_ID
                   left join   [dbo].[PROJ_APRV_STA] PAS
                      on PATS.PROJ_APRV_STA_ID = PAS.PROJ_APRV_STA_ID
                   right outer join dbo.PROJ_APRV_TYPE PAT 
                      on PATS.PROJ_APRV_TYPE_ID = PAT.PROJ_APRV_TYPE_ID
                 ) s
                 cross apply
                 (
                    select 'PROJ_APRV_TYPE_DS', PROJ_APRV_TYPE_DS union all
                    select ' Expected Date', APRV_EXPT_BY_DT union all
                    select ' Completed Date', APRV_CMPL_DT
                 ) c (col, value)
             ) data
             PIVOT
             (   
                MAX(value)
                FOR PROJ_APRV_TYPE_DS IN ([RMC Approval],[RMC Approval Expected Date],[RMC Approval Completed Date],[BOD Approval],[BOD Approval Expected Date],[BOD Approval Completed Date]) 
             ) pvt1 where PROJ_ID is not null ) AS Approval ON ProdType.PROJ_ID = Approval.PROJ_ID 
  LEFT JOIN (SELECT  *
              FROM    (   SELECT PROJ.PROJ_ID,
                                  ORG_SHRT_NM                                
                          FROM  dbo.Project PROJ  
                           left join dbo.PROJ_LGL_ENT_IMPCT  PLEI on PROJ.PROJ_ID = PLEI.PROJ_ID
                           right outer join dbo.LGL_ENT LE on PLEI.LGL_ENT_ID = LE.LGL_ENT_ID
                      ) data
  
                       PIVOT
                      (   MAX(ORG_SHRT_NM)
                          FOR ORG_SHRT_NM IN ([AECB],[FSB],[TRS])
                      ) pvt3
  
                      where PROJ_ID is not null) AS LegalEntity ON ProdType.PROJ_ID = LegalEntity.PROJ_ID  LEFT JOIN 
  
                      (;with cte as 
                      (
            SELECT PCGU.PROJ_ID,
              name = u.USER_LST_NM + ', '+ u.USER_FIRST_NM,           
              CTC_GRP_DS                               
            FROM  dbo.[user] u  
            left join dbo.PROJ_CTC_GRP_USER  PCGU 
              on u.USER_ID = PCGU.USER_ID
            left join dbo.CTC_GRP CG 
              on PCGU.CTC_GRP_ID = CG.CTC_GRP_ID
          ) 
          select *
          from
          (
            select c1.proj_id,
              c1.CTC_GRP_DS,
              STUFF(
                   (SELECT ', ' + c2.name
                    FROM cte c2
                    where c1.proj_id = c2.proj_id
                      and c1.CTC_GRP_DS = c2.CTC_GRP_DS
                    FOR XML PATH (''))
                    , 1, 1, '')  AS name
            from cte c1
          ) d     
  
          pivot
          (
            max(name)
            for CTC_GRP_DS in ([Bank Contact],[Dept2])
          ) piv 
          where PROJ_ID is not null) 
          AS Dept ON ProdType.PROJ_ID = Dept.PROJ_ID 
  </code></pre>
  
  <p>I am getting error 
  Msg 102, Level 15, State 1, Line 84
  Incorrect syntax near ';'.
  Msg 102, Level 15, State 1, Line 115
  Incorrect syntax near ')'.</p>
  
  <p>I am totally confused what i am missing it. I recently started pivot &amp; dynamic query concepts...Please guide on it...</p>