1

I have the following sql command

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',['+ cast(Month as nvarchar(2))+']' , '['+ Cast(Month as nvarchar(2))+']')
FROM    (select distinct Month from Employee  WHERE  Year*100+Month BETWEEN 201704 and 201712 ) as e
PRINT @cols

The result was

[9],[12],[6],[7],[10],[4],[5],[11],[8]

But I really want it to result in sort order

[4],[5],[6],[7],[8],[9],[10],[11],[12]
Dale K
  • 25,246
  • 15
  • 42
  • 71
Marwan Almukh
  • 201
  • 3
  • 12
  • Gives an error Msg 1033, Level 15, State 1, Line 3 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. – Marwan Almukh Dec 27 '21 at 04:09
  • @NassimH What makes you think that would necessarily get different results? Ultimately the final `SELECT` may return results in any order, and may not aggregate properly anyway – Charlieface Dec 27 '21 at 04:27

1 Answers1

6

Variable coalescing is documented as being non-deterministic, and may cause incorrect results, in particular in the presence of ORDER BY. You can also not place ORDER BY in a derived table or view, for obvious reasons: the final ordering is determined only by the outer query.

You should instead just use STRING_AGG to aggregate. You can use WITHIN GROUP (ORDER BY to get the ordering.

Note also:

  • Always use QUOTENAME to get brackets round your column names, instead of doing it yourself, as escaping can be complex.
  • It's better to make exact comparisons on columns, rather than doing calculations on them and then comparing, as then you can hit indexes (sarge-ability).
  • It's probably better to store dates in actual date columns, rather than messing around with multiple columns, but I will leave database redesign to you
DECLARE @cols NVARCHAR(MAX) = (
    SELECT STRING_AGG(QUOTENAME(Month), N',') WITHIN GROUP (ORDER BY Month)
    FROM (
        select distinct
          Month
        from Employee
        WHERE Year = 2017
          AND Month BETWEEN 4 AND 12
    ) as e
);

For SQL Server 2016 and earlier, you can use the old FOR XML method:

DECLARE @cols NVARCHAR(MAX) = STUFF((
    SELECT N',' + QUOTENAME(Month)
    FROM (
        select distinct
          Month
        from Employee
        WHERE Year = 2017
          AND Month BETWEEN 4 AND 12
    ) as e
    ORDER BY Month
    FOR XML PATH(''), TYPE
  ).value('text()[1]','nvarchar(max)')
  , 1, LEN(N','), N'');
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I receive this error: Msg 195, Level 15, State 10, Line 2 'STRING_AGG' is not a recognized built-in function name. Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'as'. I am using sql server 2016 – Marwan Almukh Dec 27 '21 at 04:37
  • OK given you a solution for older versions also – Charlieface Dec 27 '21 at 04:50
  • Worked perfectly, I am also using my first comparison (Year*100+month between ...) because some times need more than one year. – Marwan Almukh Dec 27 '21 at 04:59
  • You should still use `AND/OR` logic, eg `WHERE (Year = 2017 AND Month BETWEEN 4 AND 12) OR (Year = 2018 AND Month BETWEEN 1 AND 5)` – Charlieface Dec 27 '21 at 07:10