SQL006 (1/14/2013)
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
The following is the edited version of your above sql. I've marked the changes that I made in bold. It gets you the required results :
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)
If you still don't understand then print the variable @product for both the queries...yours and mine....you'll see the difference.
Hope this worked out for you.