SET @str=”;
SELECT @str=@str+col1+’,’ FROM MyTestTable ORDER BY col1;
SELECT @str;
GO

— We take this as granted. But does it always work?

— It does NOT work if ordered by a computed column in the subquery
DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY t.tmpcol;
SELECT @str;
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+’_’+col2 AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
GO

— It does NOT work if ordered by a computed column in the subquery
— even just data type change

DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
SET @str=”;
— even value not changed at all
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+” AS tmpcol FROM MyTestTable) t ORDER BY tmpcol;
SELECT @str;
GO
— It works if ordered by a physical column in the subquery
DECLARE @str nvarchar(max);
SET @str=”;
SELECT @str=@str+tmpcol+’,’ FROM (SELECT col2, col1+CAST(id as nvarchar(20)) AS tmpcol FROM MyTestTable) t ORDER BY col2;
SELECT @str;
GO

— It is the same result if change the table MyTestTable to have PK on any column.
— Tested on SQL Server 2008R2 RTM Developer Edition