CREATE TABLE #Test
		(
			ProductID int,
			MainMasterFeatureID int,
			--MasterFeatureValue Varchar(100),
			ChilFeatureName varchar(100),
			ParentFeatureName varchar(100)
		)
		
		INSERT INTO #Test
		SELECT 40,1,,'Pack','Type' 
		UNION ALL 
		SELECT 40,0,'Laminate','Technology'
		UNION ALL
		SELECT 40,11,'Yes','Coated'
		UNION ALL
		SELECT 52,1,'Roll','Type'
		UNION ALL
		SELECT 52,11,'NO','Coated'
		
		
		
		
		SELECT * FROM #Test

ProductID   Type    Technology      Coated
40          Pack     Laminate         YES
52          Roll       Null           No  

CREATE TABLE #Test
		(
			ProductID int,
			MainMasterFeatureID int,
			MasterFeatureValue Varchar(100),
			ChilFeatureName varchar(100),
			ParentFeatureName varchar(100)
		);
		
INSERT INTO #Test
SELECT 40,1,'1','Pack','Type' 
UNION ALL 
SELECT 40,0,'Laminate','Technology','Technology'
UNION ALL
SELECT 40,11,'1','Yes','Coated'
UNION ALL
SELECT 52,1,'1','Roll','Type'
UNION ALL
SELECT 52,11,'1','NO','Coated';

select * from #Test;
go

select
    ProductID,
    max(case when ParentFeatureName = 'Type'
            then case when MainMasterFeatureID = 0 then MasterFeatureValue else ChilFeatureName end
        end) as [Type],
    max(case when ParentFeatureName = 'Technology'
            then case when MainMasterFeatureID = 0 then MasterFeatureValue else ChilFeatureName end
        end) as [Technology],
    max(case when ParentFeatureName = 'Coated'
            then case when MainMasterFeatureID = 0 then MasterFeatureValue else ChilFeatureName end
        end) as [Coated]
from
    #Test
group by
    ProductID;

go
drop table #Test;
go

/*
ProductID   Type    Technology      Coated
40          Pack     Laminate         YES
52          Roll       Null           No  
*/

DECLARE @ParentFeatureName varchar(max)
SELECT @ParentFeatureName = STUFF((select distinct ',['+'ParentFeatureName'+']' from #Test for xml path(' ')),1,1,'')
--error is coming here to get the distict value 

SELECT @ParentFeatureName = (
SELECT SUBSTRING(
		(SELECT DISTINCT ',' + CAST(ParentFeatureName as Varchar(MAX)) FROM #Test FOR XML PATH('')),2,2000000)) 



SELECT 
ProductID,'+@ParentFeatureName+'
FROM
(SELECT * FROM #Test) AS D
PIVOT
(
			MIN(MasterFeatureValue)
			FOR  ParentFeatureName IN ('+@ParentFeatureName+') 
		) AS DD
Group By ProductID


DECLARE @sql VARCHAR(max)

SET @sql = '

SELECT 
ProductID,'+@ParentFeatureName+'
FROM
(SELECT * FROM #Test) AS D
PIVOT
(
			MIN(MasterFeatureValue)
			FOR  ParentFeatureName IN ('+@ParentFeatureName+') 
		) AS DD
Group By ProductID'

EXEC @sql

--Creating Table 

CREATE TABLE #Test
		(
			ProductID int,
			MainMasterFeatureID int,
			--MasterFeatureValue Varchar(100),
			ChilFeatureName varchar(100),
			ParentFeatureName varchar(100)
		)


--Inserting Sample Data 
		
INSERT INTO #Test
SELECT 40,1,'Pack','Type' 
UNION ALL 
SELECT 40,0,'Laminate','Technology'
UNION ALL
SELECT 40,11,'Yes','Coated'
UNION ALL
SELECT 52,1,'Roll','Type'
UNION ALL
SELECT 52,11,'NO','Coated'


--Dynamic Cross Tab Query

Declare @sql Varchar(MAX) 
Select @sql  = 'SELECT ProductID, ' 
Select @sql = @sql + STUFF((Select DISTINCT ',MAX(Case When ParentFeatureName = ' + CHAR(39) + ParentFeatureName + CHAR(39) + ' Then ChilFeatureName Else ' + CHAR(39) + CHAR(39) + ' End) As ' + ParentFeatureName From #Test For XML PATH('')),1,1,'') 
Select @sql = @sql + ' FROM #Test Group By ProductID' 
Execute(@sql) 

	CREATE TABLE #tProduct
		(
			tProductID bigint PRIMARY KEY, 
			tProductCode nvarchar(128), 
			tProductName nvarchar(256), 
			tManufacturerName nvarchar(256),
			tProductDescription nvarchar(4000)
		)


EXECUTE ('SELECT * 
		FROM #tProduct AS P
		JOIN(SELECT ProductID,  +STUFF((Select DISTINCT '',MAX(Case When ParentFeatureName = '' + CHAR(39) + ParentFeatureName + CHAR(39) + '' Then ChilFeatureName Else '' + CHAR(39) + CHAR(39) + '' End) As '' + ParentFeatureName From #Test For XML PATH('')),1,1,'''') 
  +  FROM #Test Group By ProductID'' ) AS A ON P.tProductID = A.ProductID') 

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.

CREATE TABLE #Test
		(
			ProductID int,
			MainMasterFeatureID int,
			--MasterFeatureValue Varchar(100),
			ChilFeatureName varchar(100),
			ParentFeatureName varchar(100)
		)


--Inserting Sample Data 
		
INSERT INTO #Test
SELECT 40,1,'Pack','Type' 
UNION ALL 
SELECT 40,0,'Laminate','Technology'
UNION ALL
SELECT 40,11,'Yes','Coated'
UNION ALL
SELECT 52,1,'Roll','Type'
UNION ALL
SELECT 52,11,'NO','Coated'


CREATE TABLE #tProduct
    (
        tProductID int PRIMARY KEY, 
        tProductCode nvarchar(128), 
        tProductName nvarchar(256)
    )
INSERT INTO #tProduct
SELECT 40,'001','ABC'
UNION ALL
SELECT 52,'002','XYZ'
UNION ALL
SELECT 50,'006','IJK'


Declare @sql Varchar(MAX) 

Select @sql  = 'SELECT ProductID, ' +STUFF((Select DISTINCT ',MAX(Case When ParentFeatureName = ' + CHAR(39) + ParentFeatureName + CHAR(39) + ' Then ChilFeatureName Else ' + CHAR(39) + CHAR(39) + ' End) As ' + ParentFeatureName From #Test For XML PATH('')),1,1,'') 
  + ' FROM #Test Group By ProductID' 


  DECLARE @Product varchar(max)
  SET @Product = '(SELECT *
					FROM #tProduct) AS P JOIN'+@SQL+' AS T ON P.tProductID = T.ProductID'

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

tProductID   tProductName        Type             Technology      Coated 

   40            ABC              Pack              Laminate          YES 

   52            XYZ              Roll               Null              No  

ParentFeatureName + CHAR(39) + ' Then ChilFeatureName Else ' + CHAR(39) + CHAR(39) + ' End) As ' + ParentFeatureName 
From #Test 

CREATE TABLE #Test
		(
			ProductID int,
			MainMasterFeatureID int,
			--MasterFeatureValue Varchar(100),
			ChilFeatureName varchar(100),
			ParentFeatureName varchar(100)
		)


--Inserting Sample Data 
		
INSERT INTO #Test
SELECT 40,1,'Pack','Type' 
UNION ALL 
SELECT 40,0,'Laminate','Technology'
UNION ALL
SELECT 40,11,'Yes','Coated'
UNION ALL
SELECT 52,1,'Roll','Type'
UNION ALL
SELECT 52,11,'NO','Coated'


CREATE TABLE #tProduct
    (
        tProductID int PRIMARY KEY, 
        tProductCode nvarchar(128), 
        tProductName nvarchar(256)
    )
INSERT INTO #tProduct
SELECT 40,'001','ABC'
UNION ALL
SELECT 52,'002','XYZ'
UNION ALL
SELECT 50,'006','IJK'


Declare @sql Varchar(MAX) 

Select @sql  = 'SELECT ProductID, ' +STUFF((Select DISTINCT ',MAX(Case When ParentFeatureName = ' + CHAR(39) + ParentFeatureName + CHAR(39) + ' Then ChilFeatureName Else ' + CHAR(39) + CHAR(39) + ' End) As ' + ParentFeatureName From #Test For XML PATH('')),1,1,'') 
  + ' FROM #Test Group By ProductID' 


  DECLARE @Product varchar(max)
  SET @Product = '(SELECT *
					FROM #tProduct) AS P JOIN'+@SQL+' AS T ON P.tProductID = T.ProductID'

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

tProductID   tProductName        Type             Technology      Coated 

   40            ABC              Pack              Laminate          YES 

   52            XYZ              Roll               Null              No  

Declare @sql Varchar(MAX) 
DECLARE @Product varchar(max)
Select @sql  = 'SELECT ProductID, ' +STUFF((Select DISTINCT ',MAX(Case When ParentFeatureName = ' + CHAR(39) + ParentFeatureName + CHAR(39) + ' Then ChilFeatureName Else ' + CHAR(39) + CHAR(39) + ' End) As ' + ParentFeatureName From #Test For XML PATH('')),1,1,'') 
  + ' FROM #Test Group By ProductID' 
SET @Product = 'Select tProductId, tProductName, Type, Technology, Coated From (SELECT * FROM #tProduct) AS P JOIN ('+@SQL+') AS T ON P.tProductID = T.ProductID' 
Execute (@Product)