title: Convert Access Transform/Pivot functions to mssql with multiple aggregates (concatenated in one header)
body: <p>Can somebody help me to write this code in mssql? I read multiple answers on dynamic pivoting in mssql, but can not apply it to my particular code, since I don't know how to pivot multiple aggregates that are concatenated in one header.</p>
  
  <p>Here is the code in Access:</p>
  
  <pre><code>TRANSFORM SUM(Sales.Tons) AS SumOfTonsSELECT     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;
  </code></pre>
  
  <p>The problem is this:</p>
  
  <pre><code>PIVOT [Sales]![ID_Dataset] &amp; '-' &amp; YEAR([Sales]![Date_shipped]) &amp; '-M' &amp; Format(MONTH([Sales]![Date_shipped]),'00')
  </code></pre>
  
  <p>How can I pivot this in mssql?</p>
  
  <p>Appriciate your help.</p>
  
  <p>Maja</p>