I have the following code as part of a stored proc. This part of the code is designed to return the names of columns to be used later in a dynamic PIVOT.

DECLARE @Components NVARCHAR(MAX)='';

WITH CTECols AS (
SELECT cc.strName, cc.dblWeighting
FROM tblStudentComponentInformation sci INNER JOIN tblCourseComponents cc ON sci.lngExamID = cc.lngExamID
INNER JOIN tblCourses c ON cc.lngCourseID = c.lngCourseID
INNER JOIN tblStudents ON sci.strCID = tblStudents.strCID
INNER JOIN tblProgrammeSubstreams ps ON tblStudents.lngProgrammeSubstreamID = ps.lngProgrammeSubstreamID
INNER JOIN tblDegreeProgrammeCodes dpc ON ps.lngDegreeProgrammeID = dpc.lngDegreeProgrammeID
WHERE c.blnResearchProject<>0 AND dpc.blnMScProgramme<>0
)

SELECT @Components=COALESCE(@Components + ',','') 
    +  QUOTENAME(strName)
FROM (SELECT DISTINCT strName FROM CTECols) AS Headers

If I simply SELECT the results of my CTE, the results look like this:

SELECT DISTINCT strName, dblWeighting
FROM CTECols

What I want is the list of columns in descending order of weighting, like this:

[Research Paper],[Research Project Performance],[Literature Review],[Poster Presentation]

But what I get is this:

[Literature Review],[Poster Presentation],[Research Paper],[Research Project Performance]

I’ve tried to sort the data within the CTE:

WITH CTECols AS (
SELECT TOP 100 PERCENT cc.strName, cc.dblWeighting
FROM tblStudentComponentInformation sci INNER JOIN tblCourseComponents cc ON sci.lngExamID = cc.lngExamID
INNER JOIN tblCourses c ON cc.lngCourseID = c.lngCourseID
INNER JOIN tblStudents ON sci.strCID = tblStudents.strCID
INNER JOIN tblProgrammeSubstreams ps ON tblStudents.lngProgrammeSubstreamID = ps.lngProgrammeSubstreamID
INNER JOIN tblDegreeProgrammeCodes dpc ON ps.lngDegreeProgrammeID = dpc.lngDegreeProgrammeID
WHERE c.blnResearchProject<>0 AND dpc.blnMScProgramme<>0
ORDER BY dblWeighting DESC
)

And I’ve also tried sorting the output of the CTE in the statement which follows:

SELECT @Components=COALESCE(@Components + ',','') 
    +  QUOTENAME(strName)
FROM (SELECT TOP 100 PERCENT strName, MAX(dblWeighting)  AS Weighting  FROM CTECols GROUP BY strName ORDER BY MAX(dblWeighting) DESC) AS Headers

But neither of these approaches has changed what I get in my @Components variable – the component names are still not sorted in weighting order.

What am I missing?