title: Multi-part Identifier : Error while using PIVOT in SQL stored procedure
body: <p>I want to use <code>PIVOT</code> in a stored procedure to get the rows. What I've performed is:</p>
  
  <pre><code>DECLARE @cols NVARCHAR(MAX)
  DECLARE @stmt NVARCHAR(MAX)
  
  SET @cols = ''
  
  SELECT @cols = @cols + Table3.vAnalyteName+ ','
                                          FROM Table3,Table5,Table6
                                          WHERE Table3.nBAChildProjectsNo = Table5.nBaSequenceScheduleHdrNo
                                                AND Table3.cStatusIndi &lt;&gt; 'D'
  
  SET @cols = substring(@cols, 1, len(RTRIM(@cols)) -1 ) 
  
  --SET @cols = stuff(@cols, 1, 2, '')
  
  SET @stmt = 'SELECT
              Table1.nBaSequenceScheduleHdrNo,
              Table1.vSampleId,
              Table1.vSampleName,
              Table1.iSeqNo,
              Table1.vSampleType,
              Table2.vInjectionVolume,
              Table3.vAnalyteName,
              Table4.nConcentration,
              Table1.cStatusIndi
  
          FROM Table1
  
                  INNER JOIN Table5
                        ON(Table5.nBaSequenceScheduleHdrNo =  Table1.nBaSequenceScheduleHdrNo
                           AND Table5.cStatusIndi &lt;&gt; ''D'')
  
                  INNER JOIN Table2
                        ON(Table2.nBAchildProjectsNo = Table5.nBAchildProjectsNo
                           AND Table2.cStatusIndi &lt;&gt; ''D'')
  
                  INNER JOIN Table3
                        ON(Table3.nBAchildProjectsNo = Table5.nBAchildProjectsNo
                           AND Table3.cStatusIndi &lt;&gt; ''D'')
  
                  LEFT JOIN Table6
                       ON(Table6.nBAchildProjectsNo = Table5.nBAchildProjectsNo
                          AND Table6.nBAProjectAnalyteDtlNo = Table3.nBAProjectAnalyteDtlNo
                          AND Table6.cStatusIndi &lt;&gt; ''D'')
  
                  LEFT JOIN Table4
                       ON(Table4.nBAAnalyteSampleHdrNo = Table6.nBAAnalyteSampleHdrNo
                          AND Table4.vSampleID =  Table1.vSampleId
                          AND Table4.cStatusIndi &lt;&gt; ''D'')
  
          PIVOT (
                  SUM(Table4.nConcentration) FOR Table3.vAnalyteName IN ( ' + @cols + ' )
                ) AS PVT'
  exec(@stmt)
  </code></pre>
  
  <p>But I'm getting the error of <strong><code>Multipart Identifier</code></strong> as below:</p>
  
  <blockquote>
    <p>Msg 8156, Level 16, State 1, Line 38<br>
    The column 'nBaSequenceScheduleHdrNo' was specified multiple times for 'PVT'.<br>
    Msg 4104, Level 16, State 1, Line 2<br>
    The multi-part identifier "Table1.nBaSequenceScheduleHdrNo" could not be bound.<br>
    Msg 4104, Level 16, State 1, Line 3<br>
    The multi-part identifier "Table1.vSampleId" could not be bound.<br>
    Msg 4104, Level 16, State 1, Line 4<br>
    The multi-part identifier "Table1.vSampleName" could not be bound.<br>
    Msg 4104, Level 16, State 1, Line 5<br>
    The multi-part identifier "Table1.iSeqNo" could not be bound.<br>
    Msg 4104, Level 16, State 1, Line 6<br>
    The multi-part identifier "Table1.vSampleType" could not be bound.<br>
    Msg 4104, Level 16, State 1, Line 7<br>
    The multi-part identifier "Table2.vInjectionVolume" could not be bound.<br>
    ......</p>
  </blockquote>
  
  <p>I'm using PIVOT for the first time. So please bare with it. And let me know if I can handle this error with solution.</p>
  
  <p>What I want to do here is, to get all the columns in <code>SELECT</code> with <code>Analyte</code> column of PIVOT. And <code>Pivot</code> should be performed on basis of <code>Analyte</code> and <code>concentration</code> columns.</p>
  
  <p>Values of <code>Analyte</code> column needed in header and its concentraion should be filled up as row.</p>