SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL

By Jambu Krishnamurthy, (first published: 2015/10/22)

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.              

TableName

RecordCount

CreatedDate

Table1

100

14-Sep-15

Table2

200

14-Sep-15

Table3

300

14-Sep-15

Table4

400

14-Sep-15

Table5

500

14-Sep-15

Table1

111

14-Sep-14

Table2

112

14-Sep-14

Table3

113

14-Sep-14

Table4

114

14-Sep-14

Table5

115

14-Sep-14

Table1

211

14-Sep-13

Table2

212

14-Sep-13

Table3

213

14-Sep-13

Table4

214

14-Sep-13

Table5

215

14-Sep-13

Table1

311

14-Sep-12

Table2

312

14-Sep-12

Table3

313

14-Sep-12

Table4

314

14-Sep-12

Table5

315

14-Sep-12

Table1

411

14-Sep-11

Table2

412

14-Sep-11

Table3

413

14-Sep-11

Table4

414

14-Sep-11

Table5

415

14-Sep-11

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

TableName

2012

2013

Table1

311

211

Table2

312

212

Table3

313

213

Table4

314

214

Table5

315

215

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.

TableName

2011

2012

2013

Table1

411

311

211

Table2

412

312

212

Table3

413

NULL

213

Table4

414

314

214

Table5

415

315

215

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()))

TableName

2011

2012

2013

2014

2015

Table1

411

311

211

111

100

Table2

412

312

212

555

200

Table3

413

NULL

213

113

300

Table4

414

314

214

114

400

Table5

415

315

215

115

500

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.

TableName

2009

2011

2012

2013

2014

2015

Table1

NULL

411

311

211

111

100

Table2

NULL

412

312

212

555

200

Table3

NULL

413

NULL

213

113

300

Table4

NULL

414

314

214

114

400

Table5

NULL

415

315

215

115

500

Table6

413

NULL

NULL

NULL

NULL

NULL

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.

 
Total article views: 8318 | Views in the last 30 days: 13
 
Related Articles
FORUM

Insert a value from TABLE2 into @TempTable if a row into TABLE1 not in TABLE2

How insert values from TABLE2 into @TempTable if a row into TABLE1 not in TABLE2? --Query: DECLA...

FORUM

Get TempTable name

Get TempTable name for a specifice session

FORUM

insert into

how to use the insert into select and add a date inserted into table2 at same time

FORUM

Description of a TempTables

Equivalence of sp_help for temptable

FORUM

insert special char to column

insert special char to column

Tags
dynamic sql    
pivot    
 
Contribute