How to display Unit as Second Display Item for same display Order?

I work on SQL server 2012 I face Issue : I can’t arrange Features contain Unit as second Item display for same Display Order .

for feature family as example

'Family' as [Family], 'FamilyMaxValue' as [FamilyMaxValue], 'FamilyUnit' as [FamilyUnit]

I need it to be as below

'Family' as [Family], 'FamilyUnit' as [FamilyUnit],'FamilyMaxValue' as [FamilyMaxValue]

Feature and Unit and max value get same DisplayOrder for every Feature but my issue

How to get Unit as Second Display for same Display Order .

Meaning change will be Order of items separated by comma to display feature Unit as second display for same Display Order.

 create table #SplitNumberAndUnitsFinal
(
DKFeatureName  nvarchar(100),
DisplayOrder  int
)
insert into #SplitNumberAndUnitsFinal (DKFeatureName,DisplayOrder)
values
('package',1),
('packageUnit',1),
('Family',2),
('FamilyMaxValue',2),
('FamilyUnit',2),
('parts',3),
('partsMaxValue',3),
('partsUnit',3)
DECLARE @Header nvarchar(max)=( select
substring(
    (
        Select  ', '''+ DKFeatureName +''' as ['+ DKFeatureName +']' AS [text()]
        From #SplitNumberAndUnitsFinal 
        GROUP BY DKFeatureName
        ORDER BY MIN(DisplayOrder)
        --order by DisplayOrder
       
        For XML PATH ('')
    ), 2, 10000) [Columns])
    print @Header

Expected Result arrange feature Unit as second display to be as:

Feature,FeatureUnit,FeatureMaxValue according to same display Order



'package' as [package], 'packageUnit' as [packageUnit],
'Family' as [Family], 'FamilyUnit' as [FamilyUnit],'FamilyMaxValue' as [FamilyMaxValue],
'parts' as [parts], 'partsUnit' as [partsUnit],'partsMaxValue' as [partsMaxValue]

AND I don’t Need to Display it as below :

Feature,FeatureMaxValue,FeatureUnit for same Display Order

'package' as [package], 'packageUnit' as [packageUnit],
 'Family' as [Family], 'FamilyMaxValue' as [FamilyMaxValue], 'FamilyUnit' as [FamilyUnit],
 'parts' as [parts], 'partsMaxValue' as [partsMaxValue], 'partsUnit' as [partsUnit]

Answer

You need an additional column to control the display order if DisplayOrder alone is not unique. That will allow you to specify both columns in the ORDER BY clause for the desired order:

CREATE TABLE #SplitNumberAndUnitsFinal
(
    DKFeatureName  nvarchar(100),
    DisplayOrder  int,
    SecondaryDisplayOrder  int
);
INSERT INTO #SplitNumberAndUnitsFinal (DKFeatureName,DisplayOrder,SecondaryDisplayOrder)
VALUES
    ('package',1,1),
    ('packageUnit',1,2),
    ('Family',2,1),
    ('FamilyMaxValue',2,3),
    ('FamilyUnit',2,2),
    ('parts',3,1),
    ('partsMaxValue',3,2),
    ('partsUnit',3,3)
DECLARE @Header nvarchar(MAX)=( select
SUBSTRING(
    (
        SELECT  ', '''+ DKFeatureName +''' as ['+ DKFeatureName +']' AS [text()]
        FROM #SplitNumberAndUnitsFinal 
        ORDER BY DisplayOrder,SecondaryDisplayOrder
        FOR XML PATH ('')
    ), 2, 10000) [Columns]);
PRINT @Header;