CREATE TABLE #thetable
( id INT IDENTITY(1,1) NOT NULL ,
ValDate DateTime,
[Month] INT,
CurID CHAR(3),
SpotRate FLOAT
)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'cad', 1.0)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'usd', 1.3)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/1/2016',11, 'eur', 1.51)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'cad', 1.0)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'usd', 1.31)
INSERT INTO #thetable (ValDate, [Month], CurID, SpotRate) VALUES ('11/2/2016',11, 'eur', 1.5)
SELECT * FROM #thetable
SELECT
dateadd(day,datediff(day,0, t.ValDate ),0) as DATE
,[Month]
,SUM(cad) as cad
,SUM(usd) as usd
,SUM(eur) as eur
FROM
(
SELECT
ValDate
,[Month]
,CASE WHEN CurID = 'cad' THEN SpotRate END AS 'cad'
,CASE WHEN CurID = 'usd' THEN SpotRate END AS 'usd'
,CASE WHEN CurID = 'eur' THEN SpotRate END AS 'eur'
FROM #thetable
) t
GROUP BY
dateadd(day,datediff(day,0, t.ValDate ),0)
,[Month]