Help with Pivot

1

Hi

I am struggling with a Pivot in SQL Server 2014, where I need the rows to be totalled for all rows with the same [Project Reference], [Project Name] and [Workstream]. The SQL I am using is this...

Select * From dbo.fn_Table_Supply_Allocation(3558, '2016-11-07','2016-12-16')

SELECT [Project Reference],[Project Name],[Workstream],[07-Nov-16],[14-Nov-16],[21-Nov-16],[28-Nov-16],[05-Dec-16]
FROM dbo.fn_Table_Supply_Allocation(3558,'2016-11-07','2016-12-16')
PIVOT( SUM(Weektotal) FOR [WeekStart] IN ([07-Nov-16],[14-Nov-16],[21-Nov-16],[28-Nov-16],[05-Dec-16]) ) AS P

The first select is to show what is returned from the Table Function, which returns...

Project Reference	Project Name					                        Workstream	WeekStart	WeekTotal	Cost
PR00311		        Application Access Management (CAG) 2016	Project	        07-Nov-16	5.00	                1780.00  
PR00311		        Application Access Management (CAG) 2016	Project	        14-Nov-16	1.00	                 356.00
PR00311		        Application Access Management (CAG) 2016	Project	        28-Nov-16	2.00	                 712.00
PR00311		        Application Access Management (CAG) 2016	Project	        05-Dec-16	5.00	                 1780.00
PR00311		         Application Access Management (CAG) 2016	Project	       12-Dec-16	        5.00	                 1780.00

The Pivot then returns....

Project Reference	Project Name	                                                        Workstream	07-Nov-16	14-Nov-16	21-Nov-16	28-Nov-16	05-Dec-16
PR00311	                Application Access Management (CAG) 2016	Project	        NULL	        1.00	                NULL	        NULL	        NULL
PR00311	                Application Access Management (CAG) 2016	Project	        NULL	        NULL	        NULL	        2.00	                NULL
PR00311	                Application Access Management (CAG) 2016	Project	        5.00	                NULL	        NULL	        NULL	        5.00

I am trying to get just one row returned with the totals for each week summed across all the PR00311 rows

Project Reference	Project Name	                                                       Workstream	07-Nov-16	14-Nov-16	21-Nov-16	28-Nov-16	05-Dec-16
PR00311	                Application Access Management (CAG) 2016	Project	        5.00                 1.00	                NULL	        2.00                 5.00

Can someone tell me where I am going wrong ?

Thanks

2

on the second select try something like:

select ref, name, stream, sum([date1]), sum([date2]), ...

then at the end

group by ref, name, stream

3

Magic thanks

For some reason I thought Pivot grouped the rows as well. Another lesson learnt :slight_smile:

Phil