Asked by:
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 - 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