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 I will show techniques for creating pivot tables with a cursor dynamically. First, we need table, but I will create table with default inserting rand() price from 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()
  )
  )

and then inserting data to #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 was inserting to col1,col2 column #pivot table

column value1 was inserted from 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 creating 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

and result was success.