How to make group to data on pivot sql server ?

asked 2021-04-16T22:34:36.357+00:00
ahmed salah 3,126 Reputation points

I work on SQL server 2012 i need to make group by to pivot
data repeated inside pivot so i need to make group by
TO FINAL RESULT PIVOT
this is my sql script
meaning i need to group by to data result from excute
@sqldata
so How to do that please

DECLARE @result NVARCHAR(MAX)
DECLARE @col NVARCHAR(MAX)
DECLARE @sqldata NVARCHAR(MAX)

SELECT  @result = ( SELECT  STUFF(( SELECT   ',[' +  FeatureName + ']'  FROM extractreports.dbo.ctegroupfeatur  with(nolock)
group by FeatureName,displayorder,FlagBind
ORDER BY  displayorder, case 
         when FeatureName like N'Competitor %' then substring(FeatureName, len(N'Competitor ') + 1, len(FeatureName))
         when FeatureName like N'NXP %' then substring(FeatureName, len(N'NXP ') + 1, len(FeatureName))
     end,FeatureName                                 
FOR
XML PATH('')
), 1, 1, '') AS [Output]  )




   SELECT  @col = ( SELECT   ',''' + FeatureName + ''' as ''' + QUOTENAME(FeatureName) + ''''
FROM   extractreports.dbo.ctegroupfeatur  with(nolock)
group by FeatureName,displayorder,FlagBind
ORDER BY  displayorder, case 
         when FeatureName like N'Competitor %' then substring(FeatureName, len(N'Competitor ') + 1, len(FeatureName))
         when FeatureName like N'NXP %' then substring(FeatureName, len(N'NXP ') + 1, len(FeatureName))
     end,FeatureName
FOR
XML PATH('')
)




    select @sqldata =CONCAT('  
 SELECT *  Into ##FinalTable
FROM extractreports.dbo.GetFinalFeatureData with(nolock)
PIVOT(max(Value) FOR [FeatureName] IN ('+@result+')) AS PVTTable      
',
N' select  ''CompetitorPartNumber'' as ''CompetitorPartNumber'' ,''CompetitorCompany'' as ''CompetitorCompany'',''Competitors'' as ''Competitors'',''NXPPartNumber'' as ''NXPPartNumber'',''Cross Grade'' as ''Cross Grade'',''ProductName'' as ''ProductName'',''ExecutionDate'' as ''ExecutionDate''  ' +@col + ',''ComptitorUrl'' as ''ComptitorUrl'',''NxpUrl'' as ''NxpUrl'',''CompetitorNormalizedPinName'' as ''CompetitorNormalizedPinName'',''NXPNormalizedPinName'' as ''NXPNormalizedPinName''
union all
SELECT      [CompetitorPartNumber],[CompetitorCompany],[Competitors],[NXPPartNumber],[CrossGrade],[ProductName],cast(ExecutionDate as Varchar(12)) as ''ExecutionDate'', ' +@result + ',[ComptitorUrl],[NxpUrl],CompetitorNormalizedPinName,[NXPNormalizedPinName]  FROM ##FinalTable

')


EXEC (@sqldata)
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,453 questions
{count} votes