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 SQL Server using UNPIVOT example, 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.

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 dataset, so we can do that using the code below. We are also going to create a Table Type now, as in the next article we will be making a much more complex Pivot Table, this article can be found here _LinkDoc_76 .

SQL

CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row ID
MyCol NVARCHAR(10),--Value to become column
CalcCol INT--Value to be Summed
)
GO
DECLARE
 @Piv c1bs_Piv
INSERT INTO @Piv
SELECT '1','1',UNION
SELECT
 '1','s',UNION
SELECT
 '1','3',UNION
SELECT
 '2','2',UNION
SELECT
 '2','2',UNION
SELECT
 '2','3',UNION
SELECT
 '3','5',UNION
SELECT
 '3','2',UNION
SELECT
 '3','3',UNION
SELECT
 '3','3',3
To pivot the data, we will want to write the following SQL code.
SQL_Pivot_Code
And this will return the following dataset.

SQL

MyRow123
11NULL1
2NULL71
3NULL14
Ousia Logo