Added columns in a table by dynamic ways

IF OBJECT_ID('AddedDynamicColumn','P') IS NOT NULL BEGIN DROP PROC AddedDynamicColumn END GO CREATE PROCEDURE AddedDynamicColumn AS BEGIN SELECT ED.empid,E…

Author

SQLDataDev Editorial Team

Mar 19, 2026 5 min read

IF OBJECT_ID('AddedDynamicColumn','P') IS NOT NULL
BEGIN
	DROP PROC AddedDynamicColumn
END

GO

CREATE PROCEDURE AddedDynamicColumn  
AS
BEGIN
  
	SELECT ED.empid,ED.firstname, C.categoryname,SUM(OD.unitprice) as UnitPrice, SUM(OD.qty) as Qty, SUM(OD.unitprice)*SUM(OD.qty) as Amount
	INTO #KPI
	FROM . ED 
	INNER JOIN Sales.Orders O ON O.empid = ED.empid
	INNER JOIN Sales.OrderDetails OD ON OD.orderid = O.orderid
	INNER JOIN Production.Products P ON P.productid = OD.productid
	INNER JOIN Production.Categories C ON C.categoryid = P.productid
	GROUP BY ED.empid,ED.firstname, C.categoryname

----------------------------------Create a table to hold the distinct HeadName values----------------------------------
 
CREATE TABLE #DistinctHeadNames (
    RowNum INT IDENTITY(1,1),
    HeadName VARCHAR(50)
)

INSERT INTO #DistinctHeadNames (HeadName)
SELECT DISTINCT categoryname as HeadName FROM #KPI
--------------------------------------------------------------------------
    DECLARE @totalHead INT = 0
    DECLARE @CurHead INT = 1
    DECLARE @ColName VARCHAR(100) = ''
    DECLARE @SQL VARCHAR(MAX) = 'ALTER TABLE #OutputTable'
	DECLARE @SQL1 VARCHAR(MAX)= 'UPDATE #OutputTable'

----------------------Output Table------------------------------------------
    CREATE TABLE #OutputTable
    (
        empid INT
    )
	INSERT INTO #OutputTable (empid)
	SELECT distinct empid FROM #KPI
-----------------------------------------------------------------------------
SELECT @totalHead = COUNT(DISTINCT HeadName) FROM #DistinctHeadNames

IF @totalHead>0
BEGIN
	SET @CurHead = 1
	WHILE @CurHead <= @totalHead
	BEGIN
		SELECT @ColName = HeadName FROM #DistinctHeadNames WHERE RowNum = @CurHead

		IF @CurHead > 1
			SET @SQL = @SQL + ', '
		ELSE
			SET @SQL = @SQL + ' ADD '

		SET @SQL = @SQL + QUOTENAME(@ColName) + ' DECIMAL(10,2) NOT NULL DEFAULT 0.00'

		SET @CurHead = @CurHead + 1
	END
	
	EXEC(@SQL)

	SET @CurHead = 1
		WHILE @CurHead <= @TotalHead
		BEGIN
				SELECT @ColName = HeadName FROM #DistinctHeadNames WHERE RowNum = @CurHead
	
			SET @SQL1 = 'UPDATE #OutputTable SET ' + QUOTENAME(@ColName) +
							'=KPI.Amount FROM #KPI KPI WHERE KPI.empid = #OutputTable.empid AND QUOTENAME(KPI.categoryname) = '''''

		
		    EXEC(@SQL1)

			SET @CurHead = @CurHead + 1
		END

		EXEC(@SQL1)
		
END


SELECT * FROM #OutputTable
DROP TABLE #DistinctHeadNames
DROP TABLE #KPI
DROP TABLE #OutputTable

END
GO

EXEC AddedDynamicColumn

Understanding the Problem: In database management and reporting, there often arises a need for dynamic column addition in SQL stored procedures. This need typically arises when dealing with varying data structures or when the number of columns in a result set is not fixed. The challenge lies in dynamically altering the structure of a table to accommodate new columns based on changing data requirements.

Solution Explained: The provided SQL script addresses this challenge by dynamically adding columns to a temporary table named #OutputTable within a stored procedure named AddedDynamicColumn. Let's break down the solution step by step:

Temporary Tables for Data Manipulation:
- The script begins by creating temporary tables #KPI, #DistinctHeadNames, and #OutputTable to manipulate and store data temporarily.
Dynamic Column Addition:
- The script identifies distinct values of a certain category (in this case, 'categoryname') from the #KPI table and stores them in the #DistinctHeadNames table.
- It then iterates through these distinct values to dynamically construct SQL statements for adding corresponding columns to #OutputTable using ALTER TABLE commands.
Updating Values in Dynamic Columns:
- After adding dynamic columns to #OutputTable, the script updates the values of these columns based on the data retrieved from #KPI table. This is achieved by dynamically constructing UPDATE statements for each column and executing them.
Execution:
- Finally, the script executes the dynamic SQL statements to add columns and update values in #OutputTable.
Cleanup:
- Once the dynamic operations are complete, the script drops the temporary tables to clean up the environment.

Understanding the Impact:

Flexibility: This solution enables dynamic adaptation to changing data structures, providing flexibility in reporting and analysis.
Efficiency: By automating the process of column addition and data updating, the script improves efficiency in managing evolving data requirements.
Scalability: The dynamic nature of the solution allows it to scale with the data, accommodating new categories or values seamlessly.

Conclusion: Dynamic column addition in SQL stored procedures offers a powerful solution for handling varying data structures and evolving reporting needs. By leveraging temporary tables and dynamic SQL constructs, developers can build flexible and scalable solutions for dynamic data manipulation and analysis.

Added columns in a table by dynamic ways with customisation:

IF OBJECT_ID('AddedDynamicColumn','P') IS NOT NULL
BEGIN
	DROP PROC AddedDynamicColumn
END

GO

CREATE PROCEDURE AddedDynamicColumn  
AS
BEGIN
  
	SELECT ED.empid,ED.firstname, C.categoryname,SUM(OD.unitprice) as UnitPrice, SUM(OD.qty) as Qty
	INTO #KPI
	FROM . ED 
	INNER JOIN Sales.Orders O ON O.empid = ED.empid
	INNER JOIN Sales.OrderDetails OD ON OD.orderid = O.orderid
	INNER JOIN Production.Products P ON P.productid = OD.productid
	INNER JOIN Production.Categories C ON C.categoryid = P.productid
	GROUP BY ED.empid,ED.firstname, C.categoryname

----------------------------------Create a table to hold the distinct HeadName values----------------------------------
 
CREATE TABLE #DistinctHeadNames (
    RowNum INT IDENTITY(1,1),
    HeadName VARCHAR(50)
)

INSERT INTO #DistinctHeadNames (HeadName)
SELECT DISTINCT categoryname as HeadName FROM #KPI
--------------------------------------------------------------------------
    DECLARE @totalHead INT = 0
    DECLARE @CurHead INT = 1
    DECLARE @ColName VARCHAR(100) = ''
    DECLARE @SQL VARCHAR(MAX) = 'ALTER TABLE #OutputTable'
	DECLARE @SQL1 VARCHAR(MAX)= 'UPDATE #OutputTable'

----------------------Output Table------------------------------------------
    CREATE TABLE #OutputTable
    (
        empid INT
    )
	INSERT INTO #OutputTable (empid)
	SELECT distinct empid FROM #KPI
-----------------------------------------------------------------------------
SELECT @totalHead = COUNT(DISTINCT HeadName) FROM #DistinctHeadNames

IF @totalHead>0
BEGIN
	SET @CurHead = 1
	WHILE @CurHead <= @totalHead
	BEGIN
		SELECT @ColName = HeadName FROM #DistinctHeadNames WHERE RowNum = @CurHead

		IF @CurHead > 1
			SET @SQL = @SQL + ', '
		ELSE
			SET @SQL = @SQL + ' ADD '

		SET @SQL = @SQL + QUOTENAME(@ColName+' UnitPrice') + ' DECIMAL(10,2) NOT NULL DEFAULT 0.00'+','+
						  QUOTENAME(@ColName+' Qty') + ' DECIMAL(10,2) NOT NULL DEFAULT 0.00'

		SET @CurHead = @CurHead + 1
	END
	
	EXEC(@SQL)

	SET @CurHead = 1
		WHILE @CurHead <= @TotalHead
		BEGIN
				SELECT @ColName = HeadName FROM #DistinctHeadNames WHERE RowNum = @CurHead
	
			SET @SQL1 = 'UPDATE #OutputTable SET ' + QUOTENAME(@ColName+' UnitPrice') +
							'=KPI.UnitPrice FROM #KPI KPI WHERE KPI.empid = #OutputTable.empid AND QUOTENAME(KPI.categoryname+'' UnitPrice'') = '''''

			SET @SQL1 = @SQL1+' UPDATE #OutputTable SET ' + QUOTENAME(@ColName+' Qty') +
							'=KPI.Qty FROM #KPI KPI WHERE KPI.empid = #OutputTable.empid AND QUOTENAME(KPI.categoryname+'' Qty'') = '''''

		    EXEC(@SQL1)

			SET @CurHead = @CurHead + 1
		END

		EXEC(@SQL1)
		
END
-- Execute the dynamic SQL to add columns to the table

SELECT * FROM #OutputTable
DROP TABLE #DistinctHeadNames
DROP TABLE #KPI
DROP TABLE #OutputTable

END
GO

EXEC AddedDynamicColumn

Topics covered

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts