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

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

http://ritesh-a-shah.blogspot.com/2009/03/pivot-fixed-column-cross-tab-query-in.html

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