Dynamic Cross Tab Query, without Cursor -- SQL-Server 2005

Dynamic Cross Tab Query, without Cursor -- SQL-Server 2005

Cross tab query is one of the very useful functionality in real world logic. One can create Fixed column Cross tab query and Dynamic Cross Tab Query. There are two method of creating Dynamic Cross Tab Query. 1.) With Cursor 2.) Without Cursor (Multiple assignment variable). I saw the method of Dynamic Cross Tab Query with cursor in my previous article. In this article, I will show you how to create Dynamic Cross Tab Query with multiple assignment variables. Let us create one table for demonstration and insert some data in it.

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, below is the magical script to create Cross tab query

--Declaring variable to use in Cursor

DECLARE @Cols NVARCHAR(500)

SET @Cols=''

--Select distinct list of State in @Cols variable.

--We could do this with Cursor also

--but this method is much much better then cursor

SELECT @Cols=@Cols+ s.state + ', ' FROM

(SELECT DISTINCT state FROM SalesSummaryOfRegions) AS s

--removing last ',' comma from string

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

--so far, we have just state name in @Cols variable

--but now we will make it complete query

SET @Cols='SELECT Item, ' + @Cols + ' FROM SalesSummaryOfRegions '

+ ' PIVOT (SUM(TotalSales) FOR State IN ('

+ @Cols + ')) AS pivo'

--cool!!! work is done. run it!!!!

EXECUTE sp_executeSQL @Cols

Reference: Ritesh Shah