Updating Dynamic Columns On The Go

Hello Learners. I hope you all are doing fine. This particular article is about updating the dynamic columns on the go.

There are certain situations where you have a table and then you have to pivot the table depending upon the data in any specific column, and then you may have to update those columns as well.

In this article I will be creating a table, pivoting it based on the values of a specific column, and then showing you how to update the values of such columns as well.

Let’s get started.

First we will create a Test_Pivot Table and insert random data into the table.
 
Test_Pivot Table
 
Let’s check the result of the table.
 
result
 
Now we have to pivot this table on the basis of Name Column. So we will run this code to make it a pivot on the basis of Name column,
 
code
 
Here I will show you how this query will look when we print this Dynamic SQL variable @M.
 
Dynamic SQL variable
 
When you check the Global Temporary Table ##Temp you will see the data is pivoted. Here it is.
 
Global Temporary Table
 
Now we have dynamically inserted the data in the Global Temporary Table and checked the records. There are NULLs in the table so now our next step would be to update the NULLs to ZEROs as it is not presentable. Furthermore, this is just a simple example on how to update the values of Dynamic Columns on the go. You can use this logic and customize accordingly to your needs.

So, here is the code to update the table values dynamically.
  1. -- @I IS SET TO 2 AS THERE WOULD BE A DELIMITER AFTER EACH STRING OR IF YOU SET IT TO 1, ADD PLUS 1 TO THE COUNTER AT THE END  
  2. DECLARE     @I      Int = 2,                      
  3. @K      Int = LEN(@S),  -- SET @K AS THE LENGTH OF VARIABLE @S  
  4.             @SQL    NVarchar(MAX)  
  5.   
  6.     WHILE (@I < @K)  
  7.         BEGIN  
  8.             DECLARE @A Varchar(20);  
  9.   
  10. -- IF THERE IS A CHARINDEX IN THE STRING THIS WILL RUN  
  11. IF  ((SELECT CHARINDEX(',',@S)) !=0)              
  12. BEGIN  
  13.                 SET @A = SUBSTRING(@S,1,CHARINDEX(',',@S)-1);  
  14.                 SET @S = (SELECT REPLACE(@S,@A+',',''));  
  15.             END  
  16.   
  17. --IF THERE IS NO CHARINDEX IN THE STRING THIS WILL RUN  
  18.             ELSE IF ((SELECT CHARINDEX(',',@S)) =0)         BEGIN  
  19.                 SET @A = @S  
  20.                 --SET @A = SUBSTRING(@S,1,LEN(@S))  
  21.             END  
  22.             Print @A  
  23.   
  24. --DYNAMIC SQL TO UPDATE THE CONTENTS OF TEMPORARY TABLE   
  25.             SET @SQL = 'UPDATE ##Temp SET '+@A+' = 0 WHERE '+@A+' IS NULL';                                                                 EXEC SP_ExecuteSql @SQL --EXECUTING DYNAMIC SQL  
  26.           
  27. --INCREMENTING @I WITH THE LENGTH OF THE SINGLE COLUMN EXTRACTED FROM THE STRING OFDYNAMIC COLUMNS  
  28.             SET @I = @I + LEN(@A);            
  29.         END  
I apologize for the editing. But please see the entire code for updating the values of Dynamic Columns. Below is the complete code.
  1. -- UPDATING DYNAMIC COLUMNS ON THE GO --  
  2.   
  3.   
  4. /*  
  5. --CREATE A TEST TABLE  
  6. CREATE TABLE Test_Pivot  
  7. ( [YearInt,  
  8. Name Varchar(10),  
  9. Sales Int)  
  10.   
  11. --INSERT RANDOM VALUES  
  12. INSERT INTO Test_Pivot VALUES  
  13. (2010,'A',100),  
  14. (2010,'B',300),  
  15. (2010,'C',500),  
  16. (2010,'D',600),  
  17. (2011,'A',300),  
  18. (2011,'B',200),  
  19. (2011,'D',700),  
  20. (2012,'B',400),  
  21. (2012,'C',900)  
  22. */  
  23.   
  24. GO  
  25. ALTER PROC USP_UPDATE_DYNAMIC_COLUMNS  
  26. AS  
  27. BEGIN  
  28.   
  29.   
  30. -- DECLARING VARIABLES  
  31. DECLARE     @S      Varchar(MAX);  
  32.     SET @S = STUFF((SELECT DISTINCT ','+Name FROM Test_Pivot FOR XML PATH('')),1,1,''-- INSERTING STRING WITH THE DYNAMIC COLUMNS NAME  
  33.     --PRINT @S  
  34.   
  35. -- INSERTING THE PIVOT DATA INTO TEMPORARY TABLE  
  36. IF OBJECT_ID('TEMPDB.DBO.##Temp'IS NOT NULL  
  37. DROP TABLE ##Temp;  
  38.   
  39. DECLARE @M nVARCHAR(MAX)  
  40. SET @M ='  
  41.         SELECT * INTO ##Temp  
  42.         FROM   
  43.         (  
  44.         SELECT Yr as [Year],' + @S +  
  45.         ' FROM Test_Pivot  
  46.         Pivot  
  47.         (  
  48.         SUM(Sales) FOR Name IN('+ @S + ')  
  49.         )AS Pivot_Table   
  50.         )AS TAB  
  51.         '  
  52. EXEC SP_EXECUTESQL @M  
  53. -- @I IS SET TO 2 AS THERE WOULD BE A DELIMITER AFTER EACH STRING OR IF YOU SET IT TO 1, ADD PLUS 1 TO THE COUNTER AT THE END  
  54.   
  55. DECLARE     @I      Int = 2,                      
  56. @K      Int = LEN(@S),  -- SET @K AS THE LENGTH OF VARIABLE @S  
  57.             @SQL        NVarchar(MAX)  
  58.   
  59.     WHILE (@I < @K)  
  60.         BEGIN  
  61.               
  62.             DECLARE @A Varchar(20);  
  63.             IF  ((SELECT CHARINDEX(',',@S)) !=0) -- IF THERE IS A CHARINDEX IN THE STRING THIS WILL RUN  
  64.             BEGIN  
  65.                 SET @A = SUBSTRING(@S,1,CHARINDEX(',',@S)-1);  
  66.                 SET @S = (SELECT REPLACE(@S,@A+',',''));  
  67.             END  
  68.   
  69. --IF THERE IS NO CHARINDEX IN THE STRING THIS WILL RUN  
  70.             ELSE IF ((SELECT CHARINDEX(',',@S)) =0)           
  71. BEGIN  
  72.                 SET @A = @S  
  73.                 --SET @A = SUBSTRING(@S,1,LEN(@S))  
  74.             END  
  75.             Print @A  
  76.   
  77. --DYNAMIC SQL TO UPDATE THE CONTENTS OF TEMPORARY TABLE  
  78.             SET @SQL = 'UPDATE ##Temp SET '+@A+' = 0 WHERE '+@A+' IS NULL';             EXEC SP_ExecuteSql @SQL --EXECUTING DYNAMIC SQL  
  79. --INCREMENTING @I WITH THE LENGTH OF THE SINGLE COLUMN EXTRACTED FROM THE STRING OF DYNAMIC COLUMNS  
  80.             SET @I = @I + LEN(@A);            
  81.         END  
  82. END       
  83.           
  84.   
  85.   
  86. EXEC USP_UPDATE_DYNAMIC_COLUMNS  
  87. SELECT * FROM ##TEMP  
So this is how we can update the dynamic columns dynamically.

Please note that we have used a Global Temporary Table rather than Local Temporary Table. There is a reason for using this, because the ‘Select * from #Temp ‘ after the EXEC SP_ExecuteSql will not run as Select * from #Temp will be out of the scope of the EXEC SP_ExecuteSql. So this is the reason to use Global Temporary Table instead of Local Temporary Table.

Now if we see the result of ##TEMP we will see that the NULLs are replaced by ZEROs.
 
result of ##TEMP
 
Any feedback will be welcomed in order to improve the query or code. I hope this will help. Happy Learning!