CODEWITHSUNDEEP
sqlsql-server
Emma
Emma

Reputation: 562

STUFF Not Sorting Dates in SQL Pivot

I am trying to create a pivot where we have columns of week dates - The stuff command isn't sorting by dateweek, am I doing something incorrectly ? The dates do not appear in any order at all e.g. 2019-01-07, 2020-02-24,2003-01-13,2020-12-21,2019-01-21

select product, expected, CAST(weekdue AS date) as dateweek into #tempemma from weekdueview 


DECLARE @cols  AS NVARCHAR(MAX)='';


set @cols = stuff((select distinct ',' + QUOTENAME(c.dateweek) 
        FROM (SELECT TOP(99.99) PERCENT dateweek FROM #tempemma ORDER BY dateweek desc) c 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'') 

select @cols 

   DECLARE @query AS NVARCHAR(MAX)='';

set @query = 
'SELECT * from 
(
select product, expected, dateweek from #tempemma
) src
pivot 
(
sum(expected) for dateweek in (' + @cols + ')
) piv
' 


   execute(@query)

   drop table #tempemma

How can I sort this by the dateweek column ?

Upvotes: 1

Views: 210

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Here is a little example which will sort the columns

Declare @YourTable table (dateweek date)
Insert Into @YourTable values
('2019-01-07'),('2019-01-07'),('2020-02-24'),('2003-01-13'),('2020-12-21'),('2019-01-21')

Select stuff((Select distinct  ',' + QUOTENAME(CONVERT(varchar(10),dateweek, 23)) 
               From @YourTable
               Order By 1
               For XML Path('') )
            ,1,1,'')

NOTE: Corrected for Lanu's keen observation.

Upvotes: 5

Related Questions