question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft edited

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)


sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628,

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Echo

0 Votes 0 ·

Could you provide any update?

Echo

0 Votes 0 ·

0 Answers