Quote:
SET @SQLs = 'select ''ItemId'', ''IPN'',''PartnerName'',''CustomerName'',
'' + @Columns + '' union all
SELECT ItemId,IPN,PartnerName,CustomerName,' + @Columns + '
FROM
The syntax highlighting here should give you a clue.
Your first row doesn't contain the same number of columns as your pivot data. It always contains five values:
"ItemId"
"IPN"
"PartnerName"
"CustomerName"
" + @Columns + "
That last one isn't a list of feature names; it's the literal string
" + @Columns + "
.
This is the sort of thing that's best handled in the UI. But if you really want to stick with SQL, you'll need a separate variable to hold the values for the heading row:
DECLARE @Columns As varchar(max), @ColumnHeadings As varchar(max);
SELECT
@Columns = IsNull(@Columns + ', ','') + QUOTENAME(FeatureName),
@ColumnHeadings = IsNull(@ColumnHeadings + ', ', '') + '''' + Replace(FeatureName, '''', '''''') + ''''
FROM
(SELECT DISTINCT FeatureName from [CustomerLocations].[FeatureTypes]) As B
ORDER BY
B.FeatureName
;
DECLARE @SQLs As varchar(max);
SET @SQLs = 'SELECT ''ItemId'', ''IPN'', ''PartnerName'', ''CustomerName'', ' + @ColumnHeadings + '
UNION ALL
SELECT ItemId, IPN, PartnerName, CustomerName, ' + @Columns + '
FROM
(
SELECT F.ItemId, t.FeatureName, F.FeatureValue, I.IPN, I.PartnerName, FI.CustomerName
FROM [CustomerLocations].[ItemFeatures] F
INNER JOIN [CustomerLocations].[Items] I ON F.ItemId = I.ItemId
INNER JOIN CustomerLocations.FeatureTypes T ON T.FeatureId = F.FeatureId
INNER JOIN #ItemFeatures FI ON I.CustomerID = FI.CustomerID
) As PivotData
PIVOT
(
Max(FeatureValue)
FOR FeatureName IN (' + @Columns + ')
) AS PivotResult';
EXEC(@SQLs);