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.

Solution:

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 

go 

--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) 
  ) 

DECLARE cur CURSOR FOR 
  SELECT table_catalog, 
         table_schema, 
         table_name 
  FROM   information_schema.tables 
  WHERE  table_type = 'BASE TABLE' 

OPEN cur 

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

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      --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 + ']' 

      DECLARE @SQL NVARCHAR(max)=NULL 

      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 
      EXEC(@SQL) 

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

CLOSE cur 

DEALLOCATE cur 

--Prepare Final Select Query with Concate Columns for each Table in database 
SELECT databasename, 
       schemaname, 
       tablename, 
       columnlist, 
       '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:

Subscribe to: Post Comments (Atom)