Understanding Dynamic Columns and Updating Values in SQL

Introduction

In this blog post, we will delve into the world of dynamic columns and updating values in SQL. The problem presented involves two tables, tblReports and tblLimits, which are used to calculate limits for specific categories in a report. We will explore how to find all columns with 0 values in tblReports, search for their corresponding limit values in tblLimits, and update the Limit and Balance rows accordingly.

Background

Dynamic columns can be challenging to work with, especially when dealing with complex queries that involve multiple joins and unpivoting. In this post, we will discuss the importance of understanding how dynamic columns work and provide a step-by-step guide on how to use them effectively in SQL.

The Problem at Hand

We are given two tables: tblReports and tblLimits. tblReports contains reports with categories like ‘Limit’, ‘Exp’, and ‘Balance’. The values for these categories are dynamic and depend on the corresponding limit values in tblLimits.

tblLimits contains the limit values for each group.

Our goal is to update the Limit and Balance rows in tblReports based on the corresponding limit values in tblLimits.

Step 1: Create Temporary Tables and Sample Data

To begin, we need to create temporary tables and sample data for tblReports and tblLimits.

-- Create temp tables and sample data
CREATE TABLE ##tblReports (books INT, groupid INT, category VARCHAR(25), [01-01-2014] INT, [02-01-2014] INT)
INSERT INTO ##tblReports VALUES (100, 1, 'Limit', 700, 0), (100, 1, 'Exp', 70, 0), (100, 1, 'Balance', 630, 0),
(200, 1, 'Limit', 0, 900), (200, 1, 'Exp', 0, 100), (200, 1, 'Balance', 0, 800)

CREATE TABLE ##tblLimits (groupid INT, [100bookslimit] INT, [200bookslimit] INT)
INSERT INTO ##tblLimits VALUES (1, 700, 900), (2, 7, 10)

Step 2: Unpivot tblLimits

To make it easier to join tblReports with tblLimits, we need to unpivot the columns in tblLimits.

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = '
;WITH cte_unpivot AS
(
    SELECT groupid, 
           val, 
           CAST(REPLACE(col, ''bookslimit'', '''') AS INT) AS books 
    FROM ##tblLimits 
            UNPIVOT (val FOR col IN ([100bookslimit], [200bookslimit]))AS u) 
SELECT t.books, 
       t.groupid, 
       category, 
'

Step 3: Join tblReports with cte_unpivot

Next, we need to join the unpivoted columns from tblLimits with the rows in tblReports.

SELECT @sql += '
        CASE WHEN ' + QUOTENAME(name) + ' = 0 AND t.category IN (''Limit'', ''Balance'') 
            THEN c.val ELSE t.[01-01-2014] 
        END AS ' + QUOTENAME(name) + ','
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..##tblLimits') AND name NOT IN ('groupid', 'category')

-- Delete trailing comma again
SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) 

SELECT @sql += '
FROM ##tblReports t 
        LEFT JOIN cte_unpivot c 
               ON t.books = c.books 
                  AND t.groupid = c.groupid 
'

Step 4: Execute the Query

Finally, we can execute the query to get the desired output.

EXEC sp_executesql @sql

The final output will be:

books   groupid category    01-01-2014  02-01-2014
100     1       Limit       700         700
100     1       Exp         70          0
100     1       Balance     630         700
200     1       Limit       900         900
200     1       Exp         0           100
200     1       Balance     900         800

Conclusion

In this post, we explored how to use dynamic columns and unpivoting in SQL to update values in a report. We created temporary tables and sample data, unpivoted the columns in tblLimits, joined the unpivoted columns with the rows in tblReports, and executed the query to get the desired output.

Example Use Cases

• Dynamic reporting: This approach can be used for dynamic reporting where the report structure changes frequently.
• Data aggregation: The technique of unpivoting tables can be used for data aggregation, such as calculating running totals or averages.
• Ad-hoc querying: This method is useful for ad-hoc querying where the query structure is not fixed.

Best Practices

• Understand dynamic columns and their limitations.
• Use tempdb.sys.columns metadata to get column names from tables.
• Be careful when using UNPIVOT statements, as they can be slow and memory-intensive.

Last modified on 2023-10-10