SQL Server dynamic pivot table
- by user972255
In SQL Server, I have two tables TableA and TableB, based on these I need to generate a report which is kind of very complex and after doing some research I come to a conclusion that I have to go with SQL Pivot table but I dont have any idea about the SQL Pivot feature so, can anyone please help me on this. Please see the details below:
Create table TableA
(
ProjectID INT 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
Create table TableB
(
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
