Dynamic Column List –T-SQL



Create table test (Column1 INT,Column2 NVARCHAR(10),Column3 NVARCHAR(10),Column4 NVARCHAR(10))
INSERT INTO test values( 1,'A',NULL,'B'),(2,'X','Y','Z'),(3,'P','Q',null),(4,null,'W','U') 

 

DECLARE @sql AS NVARCHAR(4000)=''
Declare @tablename nvarchar(50) ='test'
  
 
SELECT  @sql= 'SELECT ' +(SELECT stuff( (SELECT '+'+ 'ISNULL('''+ COLUMN_NAME +'|''+ CAST(' + QuoteName(COLUMN_NAME )  +' as Varchar(50)),'''')'  
FROM  INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME =@tablename 
AND TABLE_SCHEMA ='dbo'  
Order By COLUMN_NAME FOR XML PATH('')),1,1,'')) 
+ ' FROM '+ @tablename
 
--print @sql
EXEC sp_executesql @sql
 

DROP TABLE test

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s