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