title: SQL Server - Complex Dynamic Pivot columns
body: <p>I have two tables "Controls" and "ControlChilds"</p>
  
  <p>Parent Table Structure:</p>
  
  <pre><code>Create table Controls(
      ProjectID Varchar(20) NOT NULL,
      ControlID INT NOT NULL,
      ControlCode Varchar(2) NOT NULL,
      ControlPoint Decimal NULL,
      ControlScore Decimal NULL,
      ControlValue Varchar(50)
  )
  </code></pre>
  
  <p>Sample Data</p>
  
  <pre><code>ProjectID | ControlID | ControlCode | ControlPoint | ControlScore | ControlValue
  P001        1           A            30.44            65           Invalid
  P001        2           C            45.30            85           Valid
  </code></pre>
  
  <p>Child Table Structure:</p>
  
  <pre><code>Create table ControlChilds(
      ControlID INT NOT NULL,
      ControlChildID INT NOT NULL,
      ControlChildValue Varchar(200) NULL 
  )
  </code></pre>
  
  <p>Sample Data</p>
  
  <pre><code>ControlID | ControlChildID | ControlChildValue
  1           100              Yes
  1           101              No
  1           102              NA  
  1           103              Others 
  2           104              Yes
  2           105              SomeValue
  </code></pre>
  
  <p>Output should be in a single row for a given ProjectID with all its Control values first &amp; followed by child control values (based on the ControlCode (i.e.) ControlCode_Child (1, 2, 3...) and it should look like this</p>
  
  <p><img src="https://i.stack.imgur.com/JQPcB.jpg" alt="Report format"></p>
  
  <p>Also, I tried this PIVOT query and I am able to get the ChildControls table values but I dont know how to get the Controls table values.</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX);
  
  DECLARE @query AS NVARCHAR(MAX);
  select @cols = STUFF((SELECT 
                          distinct ',' + 
                          QUOTENAME(ControlCode + '_Child' + CAST(ROW_NUMBER() over(PARTITION BY ControlCode ORDER BY ControlChildID) AS Varchar(25)))
                        FROM Controls C
                        INNER JOIN ControlChilds CC 
                        ON C.ControlID = CC.ControlID 
                        FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                          , 1, 1, '');
  
  SELECT @query ='SELECT *
  FROM
  (
    SELECT   
      (ControlCode + ''_Child'' + CAST(ROW_NUMBER() over(PARTITION BY ControlCode ORDER BY ControlChildID) AS Varchar(25))) As Code,
          ControlChildValue
    FROM Controls AS C
    INNER JOIN ControlChilds AS CC ON C.ControlID = CC.ControlID
  ) AS t
  PIVOT 
  (
    MAX(ControlChildValue) 
    FOR Code IN( ' + @cols + ' )' +
  ' ) AS p ; ';
  
   execute(@query);
  </code></pre>
  
  <p>Output I am getting:
  <img src="https://i.stack.imgur.com/pDZre.jpg" alt="enter image description here"></p>
  
  <p>Can anyone please help me on how to get the Controls table values in front of each ControlChilds table values?</p>