SQL Pivot on Dynamic Date Range

Question

I'm attempting to pivot counts on dates in SSMS with a dynamic date range (previous 90 days), but my dates are out of order in the results. Is there a way to put an ORDER BY in this query? Or, does someone have a better way to pivot on a dynamic date range?

declare @cols as nvarchar(max);
declare @query as nvarchar(max); 

select @cols = stuff((select distinct 
                             ',' + quotename(ColumnDate) 
                        from 
                             [Database].[Schema].[ExampleTable] as et
                         for 
                             xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

select @query = 'select *
                   from 
                        (select et.Place                   
                                ,et.ColumnDate
                                ,et.ValueToAggregate
                           from 
                                [Database].[Schema].[ExampleTable] as et) as t 
                  pivot   
                        (count(ValueToAggregate) 
                         for ColumnDate in( ' + @cols + ')' + ')  as p; ';
                        
execute(@query);




The dates in my results are out of order...

Place               |3/25/2021  |4/19/2021  |2/21/2021  |3/22/2021  |2/14/2021  |2/11/2021
Test_Facility_1     |6          |5          |0          |2          |0          |3
Test_Facility_2     |1          |0          |0          |2          |2          |2
Test_Facility_3     |0          |1          |0          |1          |1          |2
Test_Facility_4     |124        |111        |85         |83         |95         |97

Answer 1

You can use ORDER BY in your first SELECT statement between the FROM and the FOR XML. However, exactly which expression to use in the order by may depend on the data type of column ColumnDate. If it is a date column, then you can just use

ORDER BY ColumnDate