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 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 of 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)
  6. WHILE (@I < @K)
  7. BEGIN
  8. DECLARE @A Varchar(20);
  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
  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
  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
  27. --INCREMENTING @I WITH THE LENGTH OF THE SINGLE COLUMN EXTRACTED FROM THE STRING OF DYNAMIC 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 --
  4. /*
  5. --CREATE A TEST TABLE
  6. CREATE TABLE Test_Pivot
  7. ( [Year] Int,
  8. Name Varchar(10),
  9. Sales Int)
  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. */
  24. GO
  25. ALTER PROC USP_UPDATE_DYNAMIC_COLUMNS
  26. AS
  27. BEGIN
  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
  35. -- INSERTING THE PIVOT DATA INTO TEMPORARY TABLE
  36. IF OBJECT_ID('TEMPDB.DBO.##Temp') IS NOT NULL
  37. DROP TABLE ##Temp;
  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
  55. DECLARE @I Int = 2,
  56. @K Int = LEN(@S), -- SET @K AS THE LENGTH OF VARIABLE @S
  57. @SQL NVarchar(MAX)
  59. WHILE (@I < @K)
  60. BEGIN
  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
  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
  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
  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 a Local Temporary Table. There is a reason for using this, i.e., the ‘Select * from #Temp ‘ after the EXEC SP_ExecuteSql will not run because 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!