locked
How to display features workflow first then package second? RRS feed

  • 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 -

    Sunday, December 27, 2020 12:46 AM

All replies

  • 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

    Monday, December 28, 2020 2:53 AM