T-SQL: Creating Pivot table with cursor dynamically
One of the interesting topics in SQL Server are Pivot tables, but in practice time for many programmers this topic sometimes is difficult or
some programmers cannot write grammar code for this problems. In this article, we will show techniques for creating pivot tables with a cursor dynamically. First, we need a table but will create a table with default inserting rand() price from the system.
create table #pivot
(col1 int,
col2 int,
value1 decimal(6,2) default(
(case (cast(rand()+.5 as int)*-1) when 0 then 1
else -1 end)*(convert(int,rand()*100000)%10000)*rand()
)
)
Insert data into #pivot table
insert #pivot (col1,col2) values(1,1)
insert #pivot (col1,col2) values(1,2)
insert #pivot (col1,col2) values(1,3)
insert #pivot (col1,col2) values(1,4)
insert #pivot (col1,col2) values(1,5)
insert #pivot (col1,col2) values(1,6)
insert #pivot (col1,col2) values(2,1)
insert #pivot (col1,col2) values(2,2)
insert #pivot (col1,col2) values(2,3)
insert #pivot (col1,col2) values(2,4)
insert #pivot (col1,col2) values(2,5)
insert #pivot (col1,col2) values(2,6)
insert #pivot (col1,col2) values(2,7)
insert #pivot (col1,col2) values(3,1)
insert #pivot (col1,col2) values(3,2)
insert #pivot (col1,col2) values(3,3)
With this code, we are inserting to col1,col2 column #pivot table.
Column value1 was inserted from the system with rand() price.
select * from #pivot
col1 col2 value1
----------- ----------- ---------------------------------------
1 1 1014.31
1 2 33.27
1 3 6342.69
1 4 85.21
1 5 2607.97
1 6 -3432.93
2 1 -1721.79
2 2 -4844.58
2 3 -582.77
2 4 -259.22
2 5 1735.89
2 6 -1549.44
2 7 2482.01
3 1 -719.52
3 2 -5258.93
3 3 -1335.91
(16 row(s) affected)
And int next step for this data we will create a pivot table with cursor dynamically.
declare s cursor
for
select distinct col2 from #pivot order by col2
declare @col2 int,@col2str varchar(10),@sql varchar(8000)
open s
fetch s into @col2
set @sql=''
while (@@fetch_status=0)begin
set @col2str=cast(@col2 as varchar)
set @sql=@sql+',sum(case when col2=' + @col2str+'then value1
else null end) ['+@col2str+']'
fetch s into @col2
end
set @sql='select col1'+@sql+' from #pivot group by col1'
exec(@sql)
close s
deallocate s
drop table #pivot
col1 1 2 3 4 5 6 7
-------- --------- ------------------------------------------------- --------------------
1 3305.19 -302.17 1625.63 -1042.23 -4998.37 -1936.90 NULL
2 -4784.91 5696.81 1792.75 2492.30 -1279.02 -6012.10 1551.19
3 -793.50 2432.62 -1121.00 NULL NULL NULL NULL
The result was a success.