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.