How to display features workflow first then package second?

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 -

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Aman Gupta5y ago
Accepted answer
ORDER BY CASE DkFeatureName WHEN 'WorkFlow' THEN 1 ELSE 2 END, MIN(DisplayOrder),ColumnOrderBy
If DisplayOrder and ColumnOrderBy are the same for both, it's a fifty-fifty chance that get what you want. With the presumptions given, the only way out is hard-coding. That is