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: 281

Filed under:
|
|

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

Report format

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: enter image description here

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

Related posts about sql

Related posts about sql-server-2008