Post reply

How to display arrange features where order by display order and column order by

  • December 27, 2020 at 12:24 am

    #3824353

    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 so How to do that

     

     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)

  • December 28, 2020 at 1:10 am

    #3824557

    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