In this article, we will try to understand how to use the values in a column as headers to pivoted data. In the process we will also be learning how to pass dynamic columns to the PIVOT command. The PIVOT command itself will be called in a dynamic SQL statement, and to accomplish this, we will see how to create and use a User-Defined Table Type.

Let’s start right away. Here is the original table of data. The script to generate these statements will be given below.              

We will convert the data in the above table. The year values, i.e. 2011, 2012, etc., in the TableName column will be transformed as headings for new columns. The output that we desire is shown below.

              TableName        2011      2012       2013       2014      2015

Table1              411        311        211        111        100

Table2              412        312        212        112        200

Table3              413        313        213        113        300

Table4              414        314        214        114        400

Table5              415        315        215        115        500

Let’s get started.

Step 1 - Create the tables

The first thing we need to do is to create a User-Defined Table Type and here is the command for doing this.

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
    WHERE st.name = N'TableType' AND ss.name = N'dbo')
    begin
    drop type dbo.TableType
    end
    create type dbo.TableType as table(TableName varchar(100), RecordCount int, CreatedDate datetime2)
    go 

Unfortunately the following 7 steps have to be executed at one time as a single batch, meaning you should highlight all the 7 steps (step 2 to step 8) and execute them all. The reason for this is SQL Server does not allow us to create a real table using the create table command that is defined with a User-defined Table Type.

I will explain each step in detail below, though they need to be executed as one batch.

Step 2 - Create the Table Variable

Create a table variable using the type we created in step 1.

declare @TempTable as dbo.TableType;

Step 3 - Insert Data

This step will insert the following rows containing the sample data into the table variable.

insert into @TempTable values('Table1', 100, getdate())
insert into @TempTable values('Table2', 200, getdate())
insert into @TempTable values('Table3', 300, getdate())
insert into @TempTable values('Table4', 400, getdate())
insert into @TempTable values('Table5', 500, getdate())
insert into @TempTable values('Table1', 111, dateadd(yy,-1,getdate()))
insert into @TempTable values('Table2', 112, dateadd(yy,-1,getdate()))
insert into @TempTable values('Table3', 113, dateadd(yy,-1,getdate()))
insert into @TempTable values('Table4', 114, dateadd(yy,-1,getdate()))
insert into @TempTable values('Table5', 115, dateadd(yy,-1,getdate()))
insert into @TempTable values('Table1', 211, dateadd(yy,-2,getdate()))
insert into @TempTable values('Table2', 212, dateadd(yy,-2,getdate()))
insert into @TempTable values('Table3', 213, dateadd(yy,-2,getdate()))
insert into @TempTable values('Table4', 214, dateadd(yy,-2,getdate()))
insert into @TempTable values('Table5', 215, dateadd(yy,-2,getdate()))
insert into @TempTable values('Table1', 311, dateadd(yy,-3,getdate()))
insert into @TempTable values('Table2', 312, dateadd(yy,-3,getdate()))
insert into @TempTable values('Table3', 313, dateadd(yy,-3,getdate()))
insert into @TempTable values('Table4', 314, dateadd(yy,-3,getdate()))
insert into @TempTable values('Table5', 315, dateadd(yy,-3,getdate()))
insert into @TempTable values('Table1', 411, dateadd(yy,-4,getdate()))
insert into @TempTable values('Table2', 412, dateadd(yy,-4,getdate()))
insert into @TempTable values('Table3', 413, dateadd(yy,-4,getdate()))
insert into @TempTable values('Table4', 414, dateadd(yy,-4,getdate()))
insert into @TempTable values('Table5', 415, dateadd(yy,-4,getdate()))

Step 4 - See the Original Data

This should be similar to the data presented at the beginning of the article. 

--‘Contents of Original Table’ above
select *
 from @TempTable

Step 5 - Variables

Declare the variables needed. We will use the variable @column in step 6 where we create a list of columns separated by commas.

declare @columns nvarchar(max)
declare @sql nvarchar(max)

Step 6 - Create the Columns

Create a column list from the data in the table to be used in the pivot query. This is how the output of this step will be: [2011],[2012],[2013],[2014],[2015]

Uncomment the print statement in this box and you should see this output

select @columns = coalesce(@columns + ',','') + quotename(Yr)
 from (
              select distinct Year(CreatedDate) Yr from @TempTable
              --where year(CreatedDate) between 2012 and 2013
        ) as x
b
--print @columns

Step 7 - Dynamic SQL

This is the step that creates the dynamic SQL for pivoting the data. Here we are building a Dynamic SQL for creating the pivot command using the @columns variable created in Step 6. The column list will be transformed as Headers

set @sql = '
with cte as(
                      select TableName, RecordCount, Year(CreatedDate) Yr from @TempTable
                      --where year(CreatedDate) between 2012 and 2013
                 )
                 select TableName, ' + @columns + '
                 from cte
                 pivot
                             (
                                    AVG(RecordCount) FOR Yr in ( ' + @columns + ')
                          )as PivotTable
'

Step 8 - Execute the Dynamic SQL

Finally we execute the dynamic SQL. The table variable we are passing in here has got to be a variable. It cannot be a real table. The table also has to be a table variable created using a User-defined Table type.

exec sp_executesql @sql,N'@TempTable dbo.TableType READONLY', @TempTable;

Output

Once you execute steps 2 to 8 as a single batch, you should see the data as shown under the Desired output section above. The year part of the Column values in the CreatedDate column of the original table has been converted to the columns as headers in the output. The Data in the RecordCount column has been pivoted for each table and year.

Extensions/Variations

That was the first step I wanted to demonstrate. Now let us see some powerful variations. Suppose we wanted to see the output for certain years only, for example from 2012 to 2013. Just uncomment the WHERE clause in steps 6 and 7 and execute the steps 2 to 8 again. You should see output like this. The WHERE clause should look like this.

where year(CreatedDate) between 2012 and 2013

Another scenario is seen by commenting out the following line in the original Insert statements:

insert into @TempTable values('Table3', 313, dateadd(yy,-3,getdate()))  

The output should now look like the results shown below. If there is no value for one of the tables for a specific year, that field is shown as NULL.

Now add the following INSERT statement to the list of INSERT statements. We are adding a second row in Table2 for the year 2014. This means that Table2 for 2014 will have two rows, one with a row count of 112 and one with 999. Now when you execute the entire list of statements, you should see output similar to this. For this step, be sure you comment out the WHERE clause in Steps 6 and 7

n  insert into @TempTable values('Table2', 999, dateadd(yy,-1,getdate()))

Look at the field in the results for Table2 for the year 2014. This is the average of 112 and 999 for that table for that year.

Let us do one last example. Just add this INSERT statement to the list of inserts to the original table and execute the steps 2 to 8.

insert into @TempTable values('Table6', 413, dateadd(yy,-6,getdate()))

The output should like this. See how powerful the script is. You just added a row to the original table for a single Table and year, and that is included in the output as desired.

Conclusion

There is so many possibilities for using the PIVOT command. We can use it to dynamically create headers out of the data from the table itself, as we have demonstrated in the article. You can also try the variations shown and see what results you can produce. You will see you can do a lot of transformations with it.

Thanks for reading! Hope it helps somewhere on your job.