SQL Server - Complex Dynamic Pivot columns
- by user972255
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?
