SQLTeam.com | Weblogs | Forums

How to make group to data on pivot sql server?

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)

I strongly suspect that the reason why you got no answers on SQLServerCentral for this question are all the same reasons why you might not get one here, either. You've provided no readily consumable data and you've provided nothing in the form of expected results.

2 Likes