Pivots with dynamic columns in SQL Server

Question

I am working on an SQL Query using pvots with dynamic columns in SQL Server (T-sql). Rather than submitting my lengthy query, I’m illustrating my problem with a simplified model.

I create 2 tables: Table1 and Table2 and populate them with a few entries as follows:

Table1:

---

Col_ID1...............Col_Name

1.........................Jan-11

2.........................Feb-11

3.........................Mar-11

Table2:

---

Col_ID2......Account.....AccountName......Amount

1...............121...........Electricity............10000

2...............121...........Electricity............20000

3...............121...........Electricity............30000

1...............122...........Telephone..............100

2...............122...........Telephone..............200

3...............122...........Telephone..............300

I am creating a Pivot, but I want the column names to be generated parametrically (based on dates keyed in from the input screen), and not hard coded.

The Query below works well, but gives only a few columns as foll:

Jan-11...........Feb-11...........Mar-11

10,000.00......20,000.00......30,000.00

100.00...............200.00...........300.00

I want the query to return the descriptive columns also, as foll:

Account...........AccountName...........Jan-11............Feb-11..............Mar-11

121.................Electricity..................10,000.00......20,000.00..........30,000.00

122.................Telephone.....................100.00...........200.00.............300.00

Could anybody please help me modify my query so that I can achieve my objective?

This query is an adaptation of the following article written by Dr. Andras in Sept 2007. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Somebody remarked that the code could be subject to Injection Attacks and proposed to use Quotename function instead of concatenating square brackets.

Could you explain how to use Quotename in my query.

Thanks a lot,

Leon Lai .
.
.

Here’s my Query:

------------------------ create & populate table1 --------------------------------

CREATE TABLE Table1
(Col_ID1 INT, 
Col_Name varchar(10))  

INSERT INTO Table1 VALUES (1, 'Jan-11')  
INSERT INTO Table1 VALUES (2, 'Feb-11')  
INSERT INTO Table1 VALUES (3, 'Mar-11') 

-------------------------create & populate table2 ----------------------------------

CREATE TABLE Table2  
(Col_ID2 INT,  
Account varchar(10),  
AccountName varchar(20),  
Amount numeric(18,6))  

INSERT INTO Table2 VALUES (1, 121, 'Electricity', 10000)  
INSERT INTO Table2 VALUES (2, 121, 'Electricity', 20000)  
INSERT INTO Table2 VALUES (3, 121, 'Electricity', 30000)  
INSERT INTO Table2 VALUES (1, 122, 'Telephone', 100)        
INSERT INTO Table2 VALUES (2, 122, 'Telephone', 200)   
INSERT INTO Table2 VALUES (3, 122, 'Telephone', 300)   

---------------------------------- create columns headings -------------------

DECLARE @cols NVARCHAR(2000)   
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT   
'],[' + t2.Col_Name   
FROM Table1 AS t2   
ORDER BY '],[' + t2.Col_Name 
FOR XML PATH('')   
), 1, 2, '') + ']'   

------------------------------------- create @query ----------------------

DECLARE @query NVARCHAR(4000)   

SET @query = N'SELECT '+   
@cols +' 

FROM   

--------------------------subquery-----

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

--------------------pivot -------------------------

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

----------------------exec & drop----------

EXECUTE(@query)
drop table table1
drop table table2

=======================================================

Hi Philip,

Thanks a lot for your reply.

Your proposed query works smoothly, and generates the expected screen, but it is not exactly what I wanted.

First, thanks for the code: SELECT @cols = isnull (@cols + ',' , ' ' ) + '[' + Col_Name + ']'

It is simpler and does replace my line involving stuff and xml path, apparently with same effect.

Let me explain what I want to do.

I want to develop a query in Sap Business 1 (An Accounting Package - or call it an ERP). Sap uses T-sql in Microsoft Server 2008, and has its own query generator. With very few exceptions, Sap sql is similar to T-sql.

I want my query to give a list of all income and expenses month by month over a 12-month period.

However, I don't want my column headings to be hardcoded, (as this would require me to amend my query from time to time) as follows:

Jan-11, Feb-11, Mar-11, Apr-11, ..... Dec-11

Rather, I want the column headings to be Dynamically generated from the dates which the user enters in the input screen.

As I mentioned, the query which I posted on the forum is an over-simplified version of my real query, used only for illustration. The real query contains several variables and an input screen (called Query - Selection Criteria box in Sap b1) permits the user to enter a date. It is this date which will be used to determine the column names dynamically.

This is why I needed such complicated tools as @cols, @query, pivot etc.

If I input, say '01.06.11' (01 Jun 2011) in the input screen, this date will be passed on to the sql which will determine the names of the column headings as foll:

Jun-11, Jul-11, Aug-11 ..... May-12.

If I input another date, say '01.09.10' (01 Sep 2010), the column headings will change to:

Sep-10, Oct-10, .... Aug-11

It seems that you have hardcoded my column headings.

Could you have a second look at my query, and propose something that will allow the column names to be generated parametrically instead of being hard coded?

Thanks

Leon Lai

Answer 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

Answer 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.