Missy Missy - 5 months ago 17
SQL Question

SQL Server Pivot Table Order By Clause Mystery

I am making a pivot table with customers down the left side and dates across the top so I can see how much each customer spends every day. I am totally new to this so with a lot of help from the internet, I assembled some code that works and gives me a pivot table.

The problem is that the dates are in random order. Wherever I put an order by phrase in, it gives me an error that I have to use Top or Offset in inline functions or views.

How do I get the columns sorted in order?

On a side note, any feedback to improve my code would be much appreciated. My DL_Daily table has customer number, the date and total sales for that customer for that date.

SELECT * INTO #DailyReport
FROM
(SELECT customer,salesdate [DATE], salesamt from DL_Daily where salesdate
between '2016/05/01' and '2016/05/31' ) TAB


SELECT * INTO #FileDates
FROM
(
SELECT distinct salesdate [DATE] from DL_Daily where salesdate between
'2016/05/01' and '2016/05/31'

) TAB


DECLARE @cols NVARCHAR (MAX)


SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']',
'[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
FROM (SELECT DISTINCT [DATE] FROM #DailyReport ) PV


PRINT @cols

--Now pivot it

DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT * FROM
(
SELECT * FROM #DailyReport
) x
PIVOT
(
SUM(salesamt)
FOR [DATE] IN (' + @cols + ')
) p

'
EXEC SP_EXECUTESQL @query


DL Daily Sample Data:

Customer Date Sales
1 5/1/2016 520.75
1 5/2/2016 501.75
2 5/1/2016 15.75
3 5/1/2016 150.75
3 5/2/2016 507.35
3 5/3/2016 530.75
1 5/3/2016 250.75

Results

Customer 5/1 5/3 5/2

1 520.75 250.75 501.75
2 15.25
3 150.75 530.75 507.35


Desired Results: The columns to be in this order 5/1 5/2 5/3

Answer

The order by should work. Also try with format 111 instead of 106

SELECT @cols = COALESCE (@cols + 
                         ',[' + CONVERT(NVARCHAR, [DATE], 111) + ']', 
                         '[' + CONVERT(NVARCHAR, [DATE], 111) + ']')
FROM  (  SELECT DISTINCT [DATE] 
         FROM #DailyReport 
      ) PV  
ORDER BY [DATE]