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'

this error is coming..tried a lot

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'AS'.

Now I want to join this dynamic query to #tProduct to get the desired results:

tProductID tProductName Type Technology Coated

40 ABC Pack Laminate YES

52 XYZ Roll Null No