Pivot functionwith multiple conditions (concatenated in one header)
CREATE TABLE Sales(SaleDest int, Tons decimal(18,2), DateShipped datetime, DataSet char(2));
INSERT INTO Sales VALUES
(2311, 20.4,  '20170401', 'AC'),
(2311, 9,     '20170402', 'AC'),
(2313, 1.4,   '20170405', 'AC'),
(2313, 1.4,   '20170501', 'BU'),
(2314, 29.56, '20170401', 'AC'),
(2314, 20.0,  '20170505', 'BU'),
(2320, 12,    '20170404', 'BU'),
(2320, 12,    '20170505', 'BU');
GO



I'd suggest you to use a temp table or a table variable to store the data source for the pivot solution.

I've simulated it in this way:




SELECT SaleDest, 
       Tons, 
       DataSet 
       + '-' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4)) 
       + '-M' + Format(MONTH([Sales].[DateShipped]),'00') as Caption
FROM   Sales;
GO



SaleDest | Tons  | Caption    
-------: | :---- | :----------
    2311 | 20.40 | AC-2017-M04
    2311 | 9.00  | AC-2017-M04
    2313 | 1.40  | AC-2017-M04
    2313 | 1.40  | BU-2017-M05
    2314 | 29.56 | AC-2017-M04
    2314 | 20.00 | BU-2017-M05
    2320 | 12.00 | BU-2017-M04
    2320 | 12.00 | BU-2017-M05



Now you should build columns names:




DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ',' 
                                   + QUOTENAME(DataSet 
                                   + '-' 
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4)) 
                                   + '-M' 
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @cols;
GO



| (No column name)                          |
| :---------------------------------------- |
| [AC-2017-M04],[BU-2017-M04],[BU-2017-M05] |



And finally the dynamic pivot query:




DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ',' 
                                   + QUOTENAME(DataSet 
                                   + '-' 
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4)) 
                                   + '-M' 
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @CMD = 
'SELECT SaleDest, ' + @cols + ' FROM
(
SELECT SaleDest, 
       Tons, 
       DataSet 
       + ''-'' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4)) 
       + ''-M'' + Format(MONTH([Sales].[DateShipped]),''00'') as Caption
FROM   Sales) SRC
PIVOT
(
    SUM(Tons) FOR Caption IN (' + @cols + ')
) PVT'

EXEC(@cmd);
GO



SaleDest | AC-2017-M04 | BU-2017-M04 | BU-2017-M05
-------: | :---------- | :---------- | :----------
    2311 | 29.40       | null        | null       
    2313 | 1.40        | null        | 1.40       
    2314 | 29.56       | null        | 20.00      
    2320 | null        | 12.00       | 12.00      



dbfiddle here