This is what I developed:

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

*/