Stack Exchange
SQL ordering wrong
I have the following code: 
    declare @SQLTempCols nvarchar(max)              
    declare @tempcolumns nvarchar(max)

    set @SQLTempCols = '

                    SELECT  @tempcolumns = STUFF(( SELECT DISTINCT TOP 100 PERCENT''],['' + t2.FUL_Name
                                                FROM   '+Cast( @ManagementFrom as nvarchar(max))+'.FunctionalUnitLevels AS t2
                                                ORDER BY ''],['' + t2.FUL_Name
                                                FOR XML PATH('''')), 1, 2, '''') + '']''


                    SET @tempcolumns = REPLACE(REPLACE(@tempcolumns, ''-'', ''''), '' '', '''')
                    SELECT @tempcolumns = REPLACE(@tempcolumns, '']'', ''] nvarchar(max)'')
        '


        exec sp_executesql @SQLTempCols, N'@tempcolumns nvarchar(max) OUTPUT', @tempcolumns OUTPUT

The result is fine but i want to order the columns by Fun_ID and not name.
How would I go about that?
Edit: FunctionalUnitLevels
FUL_ID | FUL_name     | FUL_Description
-----------------------------------
1      |Department    |Department
2      |Sub-Department|Sub-Department
3      |Area          |Area


Why not just change the ORDER BY statement, from ts.FUL_Name to t2.FUL_ID?

Perhaps, specifying Order by FUL_ID in your query would be more useful rather than ORDER BY ''],['' + t2.FUL_Name

@Kiril, Panagiotis Kanavos Tried that before and it gives me the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Googled that and still does not help

This is because you have not specified FUL_id in your result set. The only thing you have got there is the Full_name. If you want to order by FUL_ID you have to specify it as a column when it is selected with distinct

Instead of using DISTINCT you could use GROUP BY to get unique results (although this assumes that each Ful_Name only has one Ful_ID):
So your subquery would become something like:
SELECT  '], [' + t2.Ful_Name
FROM   T AS t2
GROUP BY t2.Ful_name, t2.Ful_ID
ORDER BY t2.Ful_ID
FOR XML PATH('''')

If each Ful_Name could have muliple values for Ful_ID then you will need to use an aggregate function to sort and maintain unique rows for Ful_Name, e.g.
SELECT  '], [' + t2.Ful_Name
FROM   T AS t2
GROUP BY t2.Ful_name
ORDER BY MIN(t2.Ful_ID)
FOR XML PATH('''')

Note - TOP 100 PERCENT is not required as you are allowed to specify an order by clause in a subquery when you use FOR XML

So depending on which one of the two scenarios above describes your situation you would either use:
declare @SQLTempCols nvarchar(max)              
declare @tempcolumns nvarchar(max)

set @SQLTempCols = '
                SELECT  @tempcolumns = STUFF(( SELECT ''],['' + t2.FUL_Name
                                            FROM   '+Cast( @ManagementFrom as nvarchar(max))+'.FunctionalUnitLevels AS t2
                                            GROUP BY t2.FUL_Name, t2.Ful_ID
                                            ORDER BY t2.Ful_ID
                                            FOR XML PATH('''')), 1, 2, '''') + '']''

                SET @tempcolumns = REPLACE(REPLACE(@tempcolumns, ''-'', ''''), '' '', '''')
                SELECT @tempcolumns = REPLACE(@tempcolumns, '']'', ''] nvarchar(max)'')
    '
exec sp_executesql @SQLTempCols, N'@tempcolumns nvarchar(max) OUTPUT', @tempcolumns OUTPUT

Or
declare @SQLTempCols nvarchar(max)              
declare @tempcolumns nvarchar(max)

set @SQLTempCols = '
                SELECT  @tempcolumns = STUFF(( SELECT ''],['' + t2.FUL_Name
                                            FROM   '+Cast( @ManagementFrom as nvarchar(max))+'.FunctionalUnitLevels AS t2
                                            GROUP BY t2.FUL_Name
                                            ORDER BY MIN(t2.Ful_ID)
                                            FOR XML PATH('''')), 1, 2, '''') + '']''

                SET @tempcolumns = REPLACE(REPLACE(@tempcolumns, ''-'', ''''), '' '', '''')
                SELECT @tempcolumns = REPLACE(@tempcolumns, '']'', ''] nvarchar(max)'')
    '
exec sp_executesql @SQLTempCols, N'@tempcolumns nvarchar(max) OUTPUT', @tempcolumns OUTPUT


like this:?                                                 SELECT   @@tempcolumns = STUFF(( SELECT
''],['' + t2.FUL_Name
FROM   '+Cast( @@ManagementFrom as nvarchar(max))+'.FunctionalUnitLevels AS t2
GROUP BY t2.Ful_name, t2.Ful_ID
ORDER BY t2.FUL_ID
FOR XML PATH('''')
  SET @@tempcolumns = REPLACE(REPLACE(@@tempcolumns, ''-'', ''''), '' '', '''')
  SELECT @@tempcolumns = REPLACE(@@tempcolumns, '']'', ''] nvarchar(max)'')


That looks about right, I have updated the answer to show the full code you would use for both scenarios I described.
