Pivots with dynamic columns in SQL Server
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
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