title: SQL Server sub query with pivot
body: <pre><code>Chromosome  Locus       Variant_A   Variant_B   Variant Strain_ID   Family  Parent1_Name    Parent1_Marker  Parent2_Name    Parent2_Marker  Line    Marker  Gid
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Gm09        40907915    G           A           GA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      BB      2
  Gm09        422384      G           A           GA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      4
  Gm09        422720      A           G           AG      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      BB      5
  Gm09        424439      C           A           CA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      7
  Gm09        425375      G           T           GT      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      9
  Gm09        425581      T           C           TC      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      BB      10
  Gm09        43921862    C           A           CA      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      AA      12
  </code></pre>
  
  <p>This is the table I have. I need this output: </p>
  
  <pre><code>| Strain     |           | Gm09_40907915 | Gm09_422384 | Gm09_422720 | Gm09_424439 |    |
  | DS11.46096 | Variant_A | G             | G           | A           | C           |    |
  | DS11.46096 | Variant_B | A             | A           | G           | A           |    |
  | DS11.46096 | Variant   | GA            | GA          | AG          |        CA    |  |
  </code></pre>
  
  <p>+------------+-----------+---------------+-------------+-------------+----------</p>
  
  <p>When I use this code:  </p>
  
  <pre><code>use nam13;
  
  DECLARE @cols NVARCHAR (MAX)
  
  SELECT @cols = COALESCE (@cols + ',[' + ChrLocus + ']', '[' + ChrLocus + ']')
             FROM    
             (
                   SELECT DISTINCT Chromosome+'_'+ CAST(Locus AS VARCHAR(10))ChrLocus 
                   FROM genotypeQA 
             ) PV 
             ORDER BY ChrLocus
  </code></pre>
  
  <p>Now pivot the result. I have written the logic inside the query </p>
  
  <pre><code>DECLARE @query NVARCHAR(MAX)
  
  SET @query = (SELECT *
           FROM 
           (  -- Source data for pivoting
              SELECT Chromosome+'_'+ CAST(Locus AS    VARCHAR(10))ChrLocus,Strain_ID,
              Variants,COLNAMES 
              FROM genotypeQA
              CROSS APPLY(VALUES (Variant_A,'Variant_A'),(Variant_B,'Variant_B'),(Variant,'Variant'))
              AS COLUMNNAMES(Variants,COLNAMES)) x
  
           PIVOT 
           (
               --Defines the values in each dynamic columns
               MIN (Variants)
  
               -- Get the names from the @cols variable to show as column
  
               FOR ChrLocus IN (['+ @cols +'])
          ) p            
      );
  
  EXEC SP_EXECUTESQL @query
  </code></pre>
  
  <p>I get the following error in SQL Server :</p>
  
  <blockquote>
    <p>Msg 116, Level 16, State 1, Line 36<br>
    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. </p>
  </blockquote>