Pivot functionwith multiple conditions (concatenated in one header) I have this query written in Microsoft Access, but I need to convert it to run on SQL Server.  I read multiple answers on dynamic pivoting in SQL Server, but can not apply it to my particular code, since I don't know how to pivot multiple conditions that are concatenated in one header.

Here is the code in Access:

TRANSFORM SUM(Sales.Tons) AS SumOfTons
SELECT     Sales.ID_SaleDest 
FROM       (QualPack 
INNER JOIN (Groups 
INNER JOIN (Customers 
INNER JOIN SaleDest 
ON         Customers.ID_Customer = SaleDest.ID_Customer) 
ON         Groups.ID_Group = SaleDest.ID_Group) 
ON         QualPack.ID_QualPack = SaleDest.ID_QualPack) 
INNER JOIN Sales 
ON         SaleDest.ID_SaleDest = Sales.ID_SaleDest 
WHERE      ((( 
                                            Year([Date_shipped]))=2017) 
           AND        (( 
                                            SaleDest.ID_Group)&lt;&gt;10) 
           AND        (( 
                                            QualPack.Quality_Second)&lt;&gt;'NO') 
           AND        (( 
                                            Sales.ID_Dataset)="AC" 
                      OR         ( 
                                            Sales.ID_Dataset)="BU")) 
OR         ((( 
                                            Year([Date_shipped]))=2016) 
           AND        (( 
                                            SaleDest.ID_Group)&lt;&gt;10) 
           AND        (( 
                                            QualPack.Quality_Second)&lt;&gt;'NO') 
           AND        (( 
                                            Sales.ID_Dataset)='AC')) 
GROUP BY   Sales.ID_SaleDest, 
           SaleDest.ID_Group 
ORDER BY   Sales.ID_SaleDest, 
           [Sales]![ID_Dataset] &amp; '-' &amp; Year([Sales]![Date_shipped]) &amp; '-M' &amp; Format(Month([Sales]![Date_shipped]),'00') 
PIVOT [Sales]![ID_Dataset] &amp; '-' &amp; YEAR([Sales]![Date_shipped]) &amp; '-M' &amp; Format(MONTH([Sales]![Date_shipped]),'00') 
WITH OWNERACCESS OPTION;


The problem is this:

PIVOT [Sales]![ID_Dataset] &amp; '-' &amp; YEAR([Sales]![Date_shipped]) &amp; '-M' 
    &amp; Format(MONTH([Sales]![Date_shipped]),'00')


How can I pivot this in mssql?

Idea is to summarize Tons for a particular SaleDest, by using Dataset, Year of Date_shipped and Month od date_shipped, but in one header (concatenated). For example, output for AC Dataset, Year 2017 and Month 1:

SaleDest    AC-2017-M01
2311        29,4
2313        2,8
2314        49,56
2320        24
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