SQL Server 2012 dynamic pivot - concatenation for

Can I use a concatenation as the cross tab variable in a dynamic pivot?

I'm trying to generate a cross tab table of values of transactions, where the column headers are [yyyy-mm] in date order based on a [transaction date] date field.

This is the data:-

 RW_Ref     ,Transaction_Date  ,Transaction_Type ,Transaction_Value ,Batch_Number ,Month
     1    ,  03/09/2009              ,11              ,30               ,999   
     1     , 05/10/2009              ,11              ,25               ,999 
     1     , 11/10/2009              ,11              ,15               ,999
     1     , 15/11/2009              ,11              ,40               ,999
     1     , 03/12/2009              ,11              ,30               ,999   
     2     , 05/10/2009              ,11              ,60               ,999 
     2     , 11/11/2009              ,11              ,70               ,999
     2     , 01/12/2009              ,11              ,60               ,999
     2     , 03/12/2009              ,11              ,15               ,999   
     3     , 05/11/2009              ,11              ,25               ,999
     3     , 06/11/2009              ,11              ,35               ,999
     3     , 07/11/2009              ,11              ,60               ,999

And this is what I'm trying to get back:-

Transaction_Type,Batch_Number,[2009-09],[2009-10],[2009-11],[2009-12]
11              ,999         ,30       ,40       ,40       ,30       
11              ,999         ,         ,60       ,70       ,75       
11              ,999         ,         ,         ,120      ,

This is the code I've got so far:-

DECLARE     @cols AS NVARCHAR(MAX),
            @query  AS NVARCHAR(MAX)

select      @cols = STUFF((SELECT distinct ', ' 
        + QUOTENAME(CONCAT(datepart(yyyy,[Transaction_Date]) , '-', RIGHT('00' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2)))
                from [TX].[dbo].[Transactions] ORDER by 1
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
       ,1,1,'')

set @query = 
'SELECT [RW_Ref], [Transaction_Type], [Transaction_Value], [Batch_Number], ' 
         + @cols 
         + ' ' 
         +'FROM [TX].[dbo].[Transactions] 
               PIVOT (
                   SUM([Transaction_Value])
              FOR
                   CONCAT(
                   datepart(yyyy,[Transaction_Date]), ' 
                   + QUOTENAME('-','''') 
                       + ', RIGHT(' + QUOTENAME('00','''') + ' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2))
               ) p '
 print @query

 execute(@query)

The Print @Query produces this:-

SELECT [RW_Ref], [Transaction_Type], [Transaction_Value], [Batch_Number],  [2009-09], [2009-10], [2009-11], [2009-12], [2016-07] FROM [TX].[dbo].[Transactions] 
PIVOT (
SUM([Transaction_Value]) 
FOR
CONCAT(
datepart(yyyy,[Transaction_Date]), '-', 
RIGHT('00' + CONVERT(NVARCHAR(2), datepart(M,[Transaction_Date])), 2))
) p

but this generates a message Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '('. Line 5 is the CONCAT( I think. Can I use the Concat fun ction to generate the column headings in this way or do I have to use a record only (which means I need to add that field as a column which I'd rather not do, it's not my table)

Thanks!

Have you tried the following code? EDIT: you need original date values both in select and in statements. You have to modify your "select @cols = ..." part of the query to return original values (get rid of the datepart stuff in the column) as column names and alias them as you wish. The IN operator requires full values.

    set @query = 
'SELECT [RW_Ref], [Transaction_Type], [Batch_Number], ' 
         + @cols 
         + ' ' 
         +'FROM [TX].[dbo].[Transactions] 
               PIVOT (
                   SUM([Transaction_Value])
              FOR

                   [Transaction_Date] IN ( ' 
                   + @cols + ')
               ) p '

Please let me know if it works. See an example below, which you can run on TSQL2012 sample database, it should help you understand how the pivot statement works.

select shipperid, [2006-07-29 00:00:00.000] as '2006/07', [2006-08-12 00:00:00.000] as '2006/08'
from sales.orders
pivot (sum (freight) for shippeddate in ([2006-07-29 00:00:00.000], [2006-08-12 00:00:00.000])) as p