Pivots with dynamic columns in SQL Server

Solution 1:

Adding those columns is very simple. The final query would be

SELECT Account, AccountName, [Feb-11],[Jan-11],[Mar-11]   FROM   
(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( [Feb-11],[Jan-11],[Mar-11] )
) AS pvt 

which has t2.AccountName added to the subquery, and Account and AccountName added to the initial SELECT. Toss them into the build statement and you're done:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT Account, AccountName, ' +    @cols +'   FROM   

(SELECT
t1.Col_Name,
t2.Account,
t2.AccountName,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p

PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( '+
@cols +' )
) AS pvt ' 

As for SQL injection, the only way I can see that happening is if someone somehow embeds malicious code within Table1.Col_Name, and if you have to worry about that, you have bigger problems than "locking down" this dynamic query.

Also worth mentioning, I'd use the following to build the list of columns (@Cols) because its shorter and easier to read, but mostly because I don't like XML.

DECLARE @cols NVARCHAR(2000)    
SELECT @cols = isnull(@cols + ',', '') + '[' + Col_Name + ']'
 FROM Table1
 ORDER BY Col_Name

Solution 2:

Adding another answer, since this the edit almost a second question. (Without details and specifics, I can only offer general outlines and psuedo code—I don’t know SAP.)

Let’s start with the pivot. It needs to generate columns labeled by, presumably, month, which you had in your example as Table1.Col_Name, a varchar(10); those values are extracted and dynamically added to the pivot query as column names. If there is no such column in the database, then you have to construct it for the query based on the data the user enters. I’ll work with the following assumptions: - The data has a dateime column, where any value (year through millisecond) may be found - The user specifies a “start date” (is it always the first of a month?) and you have to generate columns for that and the following 11 months, aggregating the data that falls within each target month.

Step 1, I’d set up and populate a temp table containing the 12 target columns:

CREATE TABLE #Months
 (
   Col_Name    varchar(10)
  ,MonthStart  datetime
  ,MonthEnd    datetime
)

Label is formatted as you want it to appear, MonthStart would be the absolute start of the month (say, Oct 1, 2011 00:00:00.000), and MonthEnd would be the absolute start of the next month (Nov 1, 2011 00:00:00.000) – this allows you to use SELECT … from <table> where DataDate >= MontStart and DataDate < MonthEnd to get all data within that month.

Next, join this table on your data table and aggregate, something like:

SELECT
   mt.Col_Name
  ,sum(dt.RawData)  Amount
 from #Months mt
  inner join MyData dt
   on dt.DataDate >= mt.MonthStart
    and dt.DataDate < mt.MonthEnd  --  Yes, ON clauses don't have to be simple equivalencies!
  inner join <other tables as necessary for Account, AccountName, etc.>

Plug this in as the innermost query of the pivot statement, extract/build the list of Col_Names from the temp table using the non-XML query (I don't know what else to call it), build and execute dynamically, and you should be good.