Easier Way To Pivot Columns Dynamically

Overview

One of my clients has been using MS Access for a long time. Now, to cope with the advancement of technology and many other advantages, they decided to move to SQL Server. My job was to facilitate the transition. Though most of MS Access queries were compatible with little or no modifications there were few which required some considerable work. This article deals

The query was very much similar to as it is below:

TRANSFORM
    Count(TestMessage)AS CountOfMessage
SELECT
    TestMessage
FROM
    SampleTable
GROUPBY
    TestMessage
PIVOT
    TransposedColumn;
It displayed count of messages from the SampleTable with all the distinct values in TransposedColumn as columns Name. It will clear when we write an equivalent SQL statement.
Since the values in the TransposedColumn have to be used as column names; I decided to use pivot (which I still think is the best option). But then I encountered a strange problem. The values were not pre-defined; columns have to be generated dynamically. And this isn’t directly supported by pivot syntax. I followed following five steps to get the pivot generate column dynamically:
  1. Created a temporary table with a column to hold all the column names.
  2. Inserted all the column names that will be used by pivot in the temporary table.
  3. Retrieved and saved those column names in a variable in the format that the PIVOT syntax accepts using COALESCE.
  4. Used the variable in pivot syntax.
  5. Dropped the table.

The query I used was very much similar to the query below:

IfExists(select 1 fromINFORMATION_SCHEMA.TABLESwhere TABLE_NAME=‘SampleTable’)
DropTable SampleTable
GO
Createtable SampleTable(
TestMessage varchar(max),
TransposedColumn varchar(max)
)
Insertinto SampleTable values (‘Message1’,‘Column1’);
Insertinto SampleTable values (‘Message1’,‘Column1’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message3’,‘Column3’);  
Insertinto SampleTable values (‘Message3’,‘Column3’);  
Insertinto SampleTable values (‘Message3’,‘Column3’);
GO
IfExists(select*from tempdb.INFORMATION_SCHEMA.TABLESwhere TABLE_NAME like‘#tempTable%’)
DropTable #temptable
Selectdistinct TransposedColumn as TestColumnname into #temptable from SampleTable
GO
Declare @columns varchar(max)
Declare @finalQuery varchar(max)
Select @columns=coalesce(@columns +‘,[‘+ TestColumnname +‘]’,  ‘[‘+ TestColumnname+‘]’                          )FROM #TempTable
Set @finalQuery=‘SELECT * FROM SampleTable   PIVOT
                       (
                        COUNT(TestMessage)  FOR TransposedColumn IN (‘+@columns+‘)  
                                                ) as PivotTable  ‘                                                
Exec  (@finalQuery )
Droptable #temptable
Now I will break the query into different parts so that it will be easy to understand. The first part will be creating a table and inserting values in it. The sample table can have as many values as it can hold but for now let us insert with values as below.
Createtable SampleTable(
TestMessage varchar(max),
TransposedColumn varchar(max)
)
Insertinto SampleTable values (‘Message1’,‘Column1’);
Insertinto SampleTable values (‘Message1’,‘Column1’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message2’,‘Column2’);
Insertinto SampleTable values (‘Message3’,‘Column3’);  
Insertinto SampleTable values (‘Message3’,‘Column3’);  
Insertinto SampleTable values (‘Message3’,‘Column3’);
 
The second will be SQL to create temporary table with required columns only.
selectdistinct TransposedColumn as TestColumnname into #temptable from SampleTable  

And the third and final part will be SQL statement that gives us the desired result.
Declare @columns varchar(max)
Declare @finalQuery varchar(max)
Select @columns=coalesce(@columns +‘,[‘+ TestColumnname +‘]’,  ‘[‘+ TestColumnname+‘]’                          )from #TempTable
Set @finalQuery=‘SELECT * FROM SampleTable   PIVOT
                       (
                        COUNT(TestMessage)  FOR TransposedColumn IN   (‘+@columns+‘)  
                                                ) as PivotTable  ‘
                                                 
Exec  (@finalQuery )

The result of the variable @columns should be as below:
Conclusion:
The above query is very much similar to simple Pivot query with fixed column names. The only difference is that it stores the column names in a variable so that it can be generated according to our requirement and later passed where required.
 

Share this article


About the author



Previous post

Next post