Dynamic Cross Tab Query (with cursor) in Microsoft SQL Server 2005

Wednesday, March 11, 2009 |

In my previous article, I explained what Cross Tab query is? And how we can create fixed column cross tab query in Microsoft SQL Server 2005 with the help of PIVOT but now I will be explaining something about dynamic cross tab query.


The row of a cross tab query used to get generated by aggregation. If you know that what column you want to be in result set, it will make your task easy. You can simply refer my previous article



But if you don’t know what column to be used and need to create it dynamically at run time than you have to go for another route rather than simple PIVOT.


Let us create the same table and data I have used in my previous article.


Create Table SalesSummaryOfRegions
(
Item VARCHAR(10) NOT NULL,
State VARCHAR(10) NOT NULL,
TotalSales INT NOT NULL
)


INSERT INTO SalesSummaryOfRegions
SELECT 'LAPTOP','CA',100 UNION ALL
SELECT 'LAPTOP','NJ',1200 UNION ALL
SELECT 'ADAPTER','CA',910 UNION ALL
SELECT 'MOUSE', 'NY',1100 UNION ALL
SELECT 'MOUSE','NY',2000

Now we will create dynamic string for column name via cursor. Cursor will iterate with our dynamic SELECT statement and that result of iteration will be assigned to generate SELECT statement. You can make it more dynamic as per your customize need.



--Declaring variable to use in Cursor
DECLARE
@strSQL NVARCHAR(500),
@strColumnList VARCHAR(50),
@bitSemi BIT

SET @bitSemi =0
SET @strSQL=''

--Forward cursor's declaration
DECLARE Col CURSOR FAST_FORWARD
FOR
--Distinct state name will be iterate in cursor and will become column name
SELECT DISTINCT State as [column] FROM SalesSummaryOfRegions Order By State
--Opening the cursor
OPEN Col
--fetch first record (state name) from cursor
FETCH Col INTO @strColumnList
--while loop iterates number of records times
WHILE @@FETCH_STATUS=0
BEGIN
--assign state name in query variable
SET @strSQL=@strSQL+@strColumnList+', '
--after assigning state, fetching next records
FETCH Col INTO @strColumnList
END
--after finishining iteration closing and deallocating cursor from memory
CLOSE Col
DEALLOCATE Col
--now creating SELECT statement from state list assigned to @strSQL variable.
SET @strSQL=LEFT(@strSQL,LEN(@strSQL)-1)
SET @strSQL='SELECT Item, ' + @strSQL
+ ' FROM SalesSummaryOfRegions PIVOT (SUM(TotalSales) for State IN ('
+ @strSQL + ')) AS piv'
--execute SELECT statement created in @strSQL
EXEC sp_executeSQL @strSQL


Reference: Ritesh Shah

0 comments: