SQL Server using PIVOT example
SQL Server using a simple PIVOT function in a real world type scenario, where the number of columns is always known.
In the last I article, I wrote about using UNPIVOT, this is the opposite function PIVOT, where it will turn rows into columns, I have used some data that may not necessarily be a real world example, but will give us enough options to play with.
Dummy data
| MyRow | MyCol | CalcCol |
| 1 | 1 | 1 |
| 1 | 3 | 1 |
| 1 | s | 1 |
| 2 | 2 | 1 |
| 2 | 2 | 6 |
| 2 | 3 | 1 |
| 3 | 2 | 1 |
| 3 | 3 | 1 |
| 3 | 3 | 3 |
| 3 | 5 | 5 |
First thing to do is create this dummy data set, so we can do that using the code below.
We are also going to create a Table Type (these can improve the performance of temp tables).
SQL
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row IDMyCol NVARCHAR(10),--Value to become columnCalcCol INT--Value to be Summed)GODECLARE @Piv c1bs_PivINSERT INTO @PivSELECT '1','1',1 UNIONSELECT '1','s',1 UNIONSELECT '1','3',1 UNIONSELECT '2','2',6 UNIONSELECT '2','2',1 UNIONSELECT '2','3',1 UNIONSELECT '3','5',5 UNIONSELECT '3','2',1 UNIONSELECT '3','3',1 UNIONSELECT '3','3',3To pivot the data, we will want to write the following SQL code.
SELECT *FROM @PivPIVOT (SUM(CalcCol)FOR MyColIN ([1],[2],[3])) AS MyTableAnd this will return the following data set. Note that where we added columns "5" and "s" above, they would be skipped with this, and in the next article we are going to ensure we capture it.
| MyRow | 1 | 2 | 3 |
| 1 | 1 | NULL | 1 |
| 2 | NULL | 7 | 1 |
| 3 | NULL | 1 | 4 |
