Post reply

How to make group to data on pivot sql server ?

  • April 16, 2021 at 11:24 pm

    #3872224

    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)

  • April 18, 2021 at 12:10 am

    #3872549

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply