SQL Server - Complex Dynamic Pivot columns
        Posted  
        
            by 
                user972255
            
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by user972255
        
        
        
        Published on 2014-06-12T14:08:37Z
        Indexed on 
            2014/06/12
            15:25 UTC
        
        
        Read the original article
        Hit count: 381
        
I have two tables "Controls" and "ControlChilds"
Parent Table Structure:
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)
)
Sample Data
ProjectID | ControlID | ControlCode | ControlPoint | ControlScore | ControlValue
P001        1           A            30.44            65           Invalid
P001        2           C            45.30            85           Valid
Child Table Structure:
Create table ControlChilds(
    ControlID INT NOT NULL,
    ControlChildID INT NOT NULL,
    ControlChildValue Varchar(200) NULL 
)
Sample Data
ControlID | ControlChildID | ControlChildValue
1           100              Yes
1           101              No
1           102              NA  
1           103              Others 
2           104              Yes
2           105              SomeValue
Output should be in a single row for a given ProjectID with all its Control values first & followed by child control values (based on the ControlCode (i.e.) ControlCode_Child (1, 2, 3...) and it should look like this

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.
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);
Output I am getting:

Can anyone please help me on how to get the Controls table values in front of each ControlChilds table values?
© Stack Overflow or respective owner