Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server - SQL Server / TSQL Tutorial

Scenario: Download Script

Sometime we need to concatenate all the columns for sql server table to generate single column.Think about a scenario where you have to generate full mail address by using First Name, Last Name, Address,City,State,Zip Code.

You can use Concate function in SQL Server or you can use + operator. In this post we are going to generate Select queries for concatenation. We don't want to put effort to type all columns for each table in a database.


As there is possibility some values could be Null in different Columns. The query I am going to generate,I have declare a variable @ReplaceNullWith with. You can set the value to any value you would like to replace Null values. You can set to 'Null' ( String Null) or ''( Blank) or 'Unknown' or anything you like. 

We will use Cursor to generate Select queries. The final results will be Database Name, Schema Name, Table Name, Column List and Select Query. You will be copying Select Query and paste in new window. 

USE yourdbname 


--Set @ReplaceNullWith variable value to value you would  
--like to Replace Null values in Columns when concatenate 
--I have replace with String Null, you can do Unknow or ''(blank) etc. 
DECLARE @ReplaceNullWith VARCHAR(50)='Null' 
DECLARE @SchemaName VARCHAR(100) 
DECLARE @TableName VARCHAR(100) 
DECLARE @DatabaseName VARCHAR(100) 

--Create Temp Table to Save Results    
IF Object_id('tempdb..#Results') IS NOT NULL 
  DROP TABLE #results 

CREATE TABLE #results 
     databasename      VARCHAR(100), 
     schemaname        VARCHAR(100), 
     tablename         VARCHAR(100), 
     columnlist        VARCHAR(max), 
     columnlistconcate VARCHAR(max) 

  SELECT table_catalog, 
  FROM   information_schema.tables 
  WHERE  table_type = 'BASE TABLE' 

OPEN cur 

FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 

      --Get List of the Columns from Table without Identity Column    
      DECLARE @ColumnList NVARCHAR(max)=NULL 

      SELECT @ColumnList = COALESCE(@ColumnList + '],[', '') + c.NAME 
      FROM   sys.columns c 
             INNER JOIN sys.tables t 
                     ON c.object_id = t.object_id 
      WHERE  Object_name(c.object_id) = @TableName 
             AND Schema_name(schema_id) = @SchemaName 
             AND is_identity = 0 

      SET @ColumnList='[' + @ColumnList + ']' 


      PRINT @ColumnList 

      DECLARE @ColumnListConcat VARCHAR(max)=NULL 

      SET @ColumnListConcat=Replace(Replace(Replace( 
                                    Replace(Replace(@ColumnList, '[', 
                                            'ISNULL(Cast(['), ']', 
                                                  '] AS VARCHAR(MAX)),''''' 
                                                             + @ReplaceNullWith 
                                    ''''')'), ',ISNULL''', '''+ISNULL'), '+', 
                                                  ',ISNULL', '+'''',''''+ISNULL' 

      PRINT @ColumnListConcat 

      SET @SQL= 'select  ''' + @DatabaseName + ''' AS DBName,' 
                + '''' + @SchemaName + ''' AS SchemaName,' + '''' 
                + @TableName + ''' AS TableName,' + '''' 
                + @ColumnList + ''' AS ColumnList,' + '''' 
                + @ColumnListConcat 
                + ''' AS ColumnListConcate' 

      PRINT @SQL 

      --Log DatabaseName,TableName,Schema Name and List of Columns in Temp Table            
      INSERT INTO #results 

      FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 

CLOSE cur 


--Prepare Final Select Query with Concate Columns for each Table in database 
SELECT databasename, 
       'Select ' + columnlistconcate + ' from  [' 
       + databasename + '].[' + schemaname + '].[' 
       + tablename + ']' AS SelectQuery 
FROM   #results 
--drop table #Results    

I ran above query and got below results. Select queries are generated with all columns concatenated for each table in a database.

How to concatenate all Columns for all tables in SQL Database Dynamically in SQL Server

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

1 comment: