```
-----This section is just to get me some test data to show you how it works
DECLARE @MyTable AS TABLE ([Date] DATE, AmountType VARCHAR(10), Price INT)
DECLARE @Date DATE = (GETDATE() - 1)
DECLARE @Price INT = 1
WHILE @Price < 61
BEGIN
DECLARE @AmountType VARCHAR(10)
SET @AmountType = CASE
WHEN @Price%4 = 0 THEN 'Type 1'
WHEN @Price%4 = 1 THEN 'Type 2'
WHEN @Price%4 = 2 THEN 'Type 3'
ELSE 'Type 4'
END
INSERT INTO @MyTable ([Date], AmountType, Price) VALUES (DATEADD(d,@Price,@Date), @AmountType, @Price * 4)
INSERT INTO @MyTable ([Date], AmountType, Price) VALUES (DATEADD(d,@Price,@Date), @AmountType, @Price * 2)
SET @Price += 1
END
SET @Date = GETDATE()
------------------Build A DateTable On The Fly-----------------
DECLARE @Dates AS TABLE ([Date] DATE, DayNum VARCHAR(4))
DECLARE @I INT = 0
WHILE @I < 60
BEGIN
INSERT INTO @Dates ([Date], DayNum) VALUES (DATEADD(d,@I,@Date),'D' + CAST(@I + 1 AS VARCHAR(4)))
SET @I += 1
END
--You can use c# to pivot this data probably a lot easier if you do just grab your query like this
SELECT
d.DayNum
,t.AmountType
,Price = SUM(ISNULL(t.Price,0))
FROM
@Dates d
LEFT JOIN @MyTable t
ON d.[Date] = t.[Date]
GROUP BY
d.DayNum
,t.AmountType
ORDER BY
d.DayNum
---- If you don't want to do the pivot in your application then you can do this:
SELECT
AmountType
,D1 = ISNULL(D1,0), D2 = ISNULL(D2,0), D3 = ISNULL(D3,0), D4 = ISNULL(D4,0), D5 = ISNULL(D5,0), D6 = ISNULL(D6,0), D7 = ISNULL(D7,0), D8 = ISNULL(D8,0), D9 = ISNULL(D9,0), D10 = ISNULL(D10,0), D11 = ISNULL(D11,0), D12 = ISNULL(D12,0), D13 = ISNULL(D13,0), D14 = ISNULL(D14,0), D15 = ISNULL(D15,0), D16 = ISNULL(D16,0), D17 = ISNULL(D17,0), D18 = ISNULL(D18,0), D19 = ISNULL(D19,0), D20 = ISNULL(D20,0), D21 = ISNULL(D21,0), D22 = ISNULL(D22,0), D23 = ISNULL(D23,0), D24 = ISNULL(D24,0), D25 = ISNULL(D25,0), D26 = ISNULL(D26,0), D27 = ISNULL(D27,0), D28 = ISNULL(D28,0), D29 = ISNULL(D29,0), D30 = ISNULL(D30,0), D31 = ISNULL(D31,0), D32 = ISNULL(D32,0), D33 = ISNULL(D33,0), D34 = ISNULL(D34,0), D35 = ISNULL(D35,0), D36 = ISNULL(D36,0), D37 = ISNULL(D37,0), D38 = ISNULL(D38,0), D39 = ISNULL(D39,0), D40 = ISNULL(D40,0), D41 = ISNULL(D41,0), D42 = ISNULL(D42,0), D43 = ISNULL(D43,0), D44 = ISNULL(D44,0), D45 = ISNULL(D45,0), D46 = ISNULL(D46,0), D47 = ISNULL(D47,0), D48 = ISNULL(D48,0), D49 = ISNULL(D49,0), D50 = ISNULL(D50,0), D51 = ISNULL(D51,0), D52 = ISNULL(D52,0), D53 = ISNULL(D53,0), D54 = ISNULL(D54,0), D55 = ISNULL(D55,0), D56 = ISNULL(D56,0), D57 = ISNULL(D57,0), D58 = ISNULL(D58,0), D59 = ISNULL(D59,0), D60 = ISNULL(D60,0)
FROM
(
SELECT
DayNum
,AmountType
,Price
FROM
@Dates d
LEFT JOIN @MyTable t
ON d.[Date] = t.[Date]
) s
PIVOT
(
SUM(Price)
FOR DayNum IN ([D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],[D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20],[D21],[D22],[D23],[D24],[D25],[D26],[D27],[D28],[D29],[D30],[D31],[D32],[D33],[D34],[D35],[D36],[D37],[D38],[D39],[D40],[D41],[D42],[D43],[D44],[D45],[D46],[D47],[D48],[D49],[D50],[D51],[D52],[D53],[D54],[D55],[D56],[D57],[D58],[D59],[D60])
) as p
```

To answer some of the comments between us. We were looking for exactly what your last comment was a table or part there of that showed the structure of the table including Date, AmountType, Price, etc. That way we could understand how you where arriving at D1, D2, etc. Run a query against @MyTable to see the data example providing a few rows of that would have solved my and others curiosity.

My personal opinion is that you should probably look at a robust dynamic SQL or .net solution to pivot the dataset rather than hard coding to handle issues such as missing dates, changing number of days, etc.

The solution I presented here uses no dynamic sql is hard coded for 60 days and shows you 2 different datasets for you to choose from. I also made an assumption that you where looking for consecutive dates so this query takes into account that a price might be missing during that time and will treat it as such.

`sql-server-2014`

. Differences in syntax and features often affect the answers. Are you looking for something that`pivot`

can do?3more comments