Click here to Skip to main content
15,891,951 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have query make pivot table based on max feature of value

i need to replace max(featurevalue) with Feature Value

meaning i dont need using aggregate function on pivot .

i need using Feature value it self

when using no aggregate as above it give me error

incorrect syntax near for

how to solve this issue please

What I have tried:

SQL
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(FeatureName)
FROM
--select distinct Features 
(select  distinct FT.FeatureName  from #ItemFeatures Feat inner join [CustomerLocations].[ItemFeatures] ItemF
on ItemF.CustomerId=Feat.CustomerId INNER join CustomerLocations.FeatureTypes FT on ItemF.FeatureId=FT.FeatureId

   ) AS B
   ORDER BY B.FeatureName
--pivot table make count for item to every Feature Based on features Name
DECLARE @SQLs as VARCHAR(MAX)

SET @SQLs = 'SELECT ItemCode, IPN,PartnerName,CustomerName,FeatureValues ' + @Columns + '
FROM
(
  select F.ItemId,F.ItemId as ItemCode,I.IPN,I.PartnerName,I.PartnerPart,Itm.CustomerName,t.FeatureName,F.FeatureValue,F.FeatureValue as FeatureValues
               from #ItemFeatures Itm 
 inner join [CustomerLocations].[ItemFeatures] F on F.CustomerId=Itm.CustomerId  
 inner join [CustomerLocations].[Items] I on I.ItemID=F.ItemId
 inner join CustomerLocations.FeatureTypes T on T.FeatureId=F.FeatureId 
 
) as PivotData
PIVOT
(
   FeatureValue
   FOR FeatureName IN (' + @Columns + ')
) AS PivotResult
ORDER BY CustomerName'

EXEC(@SQLs)
Posted
Updated 3-Feb-20 23:17pm

1 solution

The reason it gives you an error is that the PIVOT function always requires an aggregate function: that's mandatory because PIVOT is there to aggregate your results and rotate teh aggregated rows into columns!

You cannot just use a column, because that doesn't aggregate anything!

I'd suggest that you sit down and define exactly what you are trying to do with input and and output samples, before trying to force a function to do something it just isn't meant for...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900