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
(SELECT customer,salesdate [DATE], salesamt from DL_Daily where salesdate
between '2016/05/01' and '2016/05/31' ) TAB
SELECT * INTO #FileDates
SELECT distinct salesdate [DATE] from DL_Daily where salesdate between
'2016/05/01' and '2016/05/31'
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']',
'[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
FROM (SELECT DISTINCT [DATE] FROM #DailyReport ) PV
--Now pivot it
DECLARE @query NVARCHAR(MAX)
SET @query = '
SELECT * FROM
SELECT * FROM #DailyReport
FOR [DATE] IN (' + @cols + ')
EXEC SP_EXECUTESQL @query
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
Customer 5/1 5/3 5/2
1 520.75 250.75 501.75
3 150.75 530.75 507.35
order by should work. Also try with format
111 instead of
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 111) + ']', '[' + CONVERT(NVARCHAR, [DATE], 111) + ']') FROM ( SELECT DISTINCT [DATE] FROM #DailyReport ) PV ORDER BY [DATE]