SQLTeam.com | Weblogs | Forums

How to display features workflow first then package second?

How to display arrange features where order by display order and column order by are equal ?

I have two features Package and Workflow have same display order nd same columnorder by

so How to make order by same values

ORDER BY MIN(DisplayOrder),ColumnOrderBy

I need to display Workflow first then package second so How to do that please ?

my script below :

   CREATE TABLE #SplitNumberAndUnitsFinal(
     [part_id] nvarchar(20) NULL,
     [DKFeatureName] [nvarchar](255) NULL,
     [DisplayOrder] int NULL,
     [ColumnOrderBy] int NULL,
     [value] [nvarchar](255) NULL
 ) 
INSERT #SplitNumberAndUnitsFinal ([part_id], [DKFeatureName], [DisplayOrder], [ColumnOrderBy], [value]) VALUES (13587183, N'Packaging', 2, 1, N'-')
 INSERT #SplitNumberAndUnitsFinal ([part_id], [DKFeatureName], [DisplayOrder], [ColumnOrderBy], [value]) VALUES (13587171, N'Packaging', 2, 1, N'-')
 INSERT #SplitNumberAndUnitsFinal ([part_id], [DKFeatureName], [DisplayOrder], [ColumnOrderBy], [value]) VALUES (13587183, N'WorkFlow', 2, 1, N'ReadyData')
 INSERT #SplitNumberAndUnitsFinal ([part_id], [DKFeatureName], [DisplayOrder], [ColumnOrderBy], [value]) VALUES (13587171, N'WorkFlow', 2, 1, N'ReadyData')
    
 DECLARE @Header nvarchar(max) =  
     (SELECT SUBSTRING((SELECT ', ''' + CASE WHEN DKFeatureName LIKE '%Units' THEN 'Unit' WHEN DKFeatureName LIKE '%MaxValue' THEN 'Max Value'  ELSE replace(DKFeatureName,'''','''''') END + ''' AS '
         + QUOTENAME(CASE WHEN DKFeatureName LIKE '%Units' THEN 'Unit' WHEN DKFeatureName LIKE '%MaxValue' THEN 'Max Value'   ELSE DKFeatureName END) AS [text()]
     FROM #SplitNumberAndUnitsFinal 
      GROUP BY DKFeatureName,ColumnOrderBy
      ORDER BY MIN(DisplayOrder),ColumnOrderBy
      FOR XML PATH (''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])
    
     --PRINT @Header
   declare @Columns nvarchar(max)=( select
     substring(
         (
             Select  ',['+DKFeatureName +']' AS [text()]
             From #SplitNumberAndUnitsFinal 
         GROUP BY DKFeatureName,ColumnOrderBy
             
              ORDER BY MIN(DisplayOrder),ColumnOrderBy
            
               
             For XML PATH ('')
         ,TYPE).value('(./text())[1]','NVARCHAR(MAX)'), 2, 10000) [Columns])
    
    
        
         update f set f.displayorder=0,f.ColumnOrderBy=0 from #SplitNumberAndUnitsFinal f
    
    
      DECLARE @SQL NVARCHAR(MAX)
     select @SQL =CONCAT('  
  SELECT *  Into #NewTable
 FROM #SplitNumberAndUnitsFinal
 PIVOT(max(Value) FOR DKFeatureName IN ('+@Columns+')) AS PVTTable       
 ',
 N'  Select ''PART_ID'' as ''PART_ID'' ,   ' +@Header + ' 
 union all
 select PART_ID ,  ' +@Columns + '  from  #NewTable
        
    
    ')
        
 EXEC (@SQL)

expected result

PART_ID WorkFlow Packaging
PART_ID WorkFlow Packaging
13587171 ReadyData -
13587183 ReadyData -

Add this to the order by clause for @Header and @Columns

, DKFeatureName desc