How to display features workflow first then package second?

Question

User696604810 posted

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 as below :

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

Answer 1

User-939850651 posted

Hi ahmedbarbary,

I tested these SQL statements you provided, and I found that the results it got were very similar to what you expected, except that the fields were displayed in the wrong order.

So you could try a simple modification like this:

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 DKFeatureName desc,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 DKFeatureName desc,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)

--select  @Columns as columns
--select @Header as header
--drop table #SplitNumberAndUnitsFinal

Result:

Best regards,

Xudong Peng