Pivot table-Pivot the sum of the values by month for each unique reference

Transact-SQL
,
1

I'm trying to create monthlysummation of values for each unique reference.
eg.
Ref1 Apr-14 May-14 June-14
A100 2000
A200 2000 1000

However what i could get is;
Ref1 Apr-14 May-14 June-14
A100 2000
A200 2000
A200 1000

ie. A200 is in another line, not in the same line.
Below is my code. Can someone help me to get the total in same line for unique references.

CREATE TABLE [Test].[dbo].[Table1] (
[DATE] datetime,
[Ref1] nvarchar(50),
[Amount] MONEY
)

INSERT INTO [Test].[dbo].[Table1]
([DATE]
,[Ref1]
,[Amount]
)
VALUES
('2014-04-19','A100',1000)
,('2014-04-19','A100',1000)
,('2014-05-20','A200',1000)
,('2014-05-20','A200',1000)
,('2014-06-20','A200',1000)

--Code to create the pivot table
SELECT
[Ref1],
[Apr-14], [May-14], [Jun-14]
FROM
(
Select
[Ref1],
[Amount],
REPLACE(RIGHT(CONVERT(VARCHAR(9), [DATE], 6), 6), ' ', '-') AS [Mon-YY]
from
[Test].[dbo].[Table1]
) source
PIVOT
(
SUM(Amount)
FOR [Mon-YY]
IN ([Apr-14], [May-14], [Jun-14] )
) AS pvtMonth

2

I loaded your code and ran it. It returned the output you claimed you want.

Ref1 Apr-14  May-14  Jun-14
---- ------- ------- -------
A100 2000.00 NULL    NULL
A200 NULL    2000.00 1000.00

(2 row(s) affected)
1 Like