Using Dynamic Pivot Tables In SQL Server

PIVOT relational operator is one of the best features that the SQL Server consists of. PIVOT revolves around a table consisting of unique values into multiple columns. A lot of reporting companies, Data Warehouse and analytical consultancies use the PIVOT for their industrial purposes. A simple pivot just rotates the table value to multiple columns. However, if we need the values for pivot column to be automated, then dynamic PIVOT is the best option for us.

We are going to learn about using the dynamic pivot in our SQL tables.

In my example, I am going to create a table and insert the values about the list of table fragmentations. Let’s check the steps on performing a dynamic PIVOT.

Step 1: Inserted 10 random of my database fragmentation records into #1 temp table

select * from #1


Step 2: Implementing a dynamic PIVOT on #1 temp table

We are going to pivot on [date] field column, taking into account the max function on (fragmentation) column.

DECLARE @Pivot_Column [nvarchar](max); 
DECLARE @Query [nvarchar](max); 
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(date) FROM 
(SELECT DISTINCT [date] FROM #1 )Tab
SELECT @Query='SELECT TableName,DatabaseName, '+@Pivot_Column+'FROM  
(SELECT TableName, [date] , DatabaseName,Fragmentation  FROM #1   )Tab1 
PIVOT 
( 
MAX(Fragmentation) FOR [date] IN ('+@Pivot_Column+')) AS Tab2 
ORDER BY Tab2.TableName' 
EXEC   (@Query)

Ordering Dynamic PIVOT column

Currently, I have just one data in my temptable for all 10 records. But, imagine if you need to dynamically PIVOT for multiple date field columns and they should be in an ordered manner. Then, the order by clause should be added to the PIVOT parameter as shown in the code below.

DECLARE @Pivot_Column [nvarchar](max); 
DECLARE @Query [nvarchar](max); 
SELECT @Pivot_Column= COALESCE(@Pivot_Column+',','')+ QUOTENAME(date) FROM 
(SELECT DISTINCT [date] FROM #1 )Tab Order By date
SELECT @Query='SELECT TableName,DatabaseName, '+@Pivot_Column+'FROM  
(SELECT TableName, [date] , DatabaseName,Fragmentation  FROM #1   )Tab1 
PIVOT 
( 
MAX(Fragmentation) FOR [date] IN ('+@Pivot_Column+')) AS Tab2 
ORDER BY Tab2.TableName' 
EXEC   (@Query)

0 comments:

Post a Comment