Sql Server 2012 Dynamic Pivot - Concatenation For Cross Tab

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] i

Solution 1:

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-2900:00:00.000] as'2006/07', [2006-08-1200:00:00.000] as'2006/08'from sales.orders
pivot (sum (freight) for shippeddate in ([2006-07-2900:00:00.000], [2006-08-1200:00:00.000])) as p