CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012dynamic-sql
sree
sree

Reputation: 1960

Dynamic sql generation

I need to generate a dynamic sql in below specified format where my table is a parameter i.e, Number of columns is not static

For example, below may be the table schema

ID Name
1  asd
2  xyz

I need a query which generates the select statement as below

select 'ID :' + ID + ',Name :'+Name from table

The output from generated above sql will be like this ID : 1, Name:asd ID : 2, Name:xyz

If the table has more number of columns, select statement that needs to be changes varies as below

select 'ID :' + ID + ',Name :'+Name + ',Col3 :' + Col3 ...from table

Could someone help me regarding this

Thanks, Sree

Upvotes: 0

Views: 1234

Answers (3)

sree
sree

Reputation: 1960

I am able to achieve this using below sql

DECLARE @TableName VARCHAR(MAX) = 'tableName'
DECLARE @SQL VARCHAR(MAX) = 'SELECT ''{''+''' 

SELECT @SQL = @SQL + '
    "'+COLUMN_NAME+'":"''' + '+coalesce(CAST('+COLUMN_NAME+' AS VARCHAR(MAX)),'''')+''",' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
SET @SQL =  LEFT(@SQL,LEN(@SQL)-1) + '
}'' FROM ' + @TableName

PRINT @SQL

Thanks, Sree

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81950

Here is one option which uses a little XML and string manipulation

I should add, NULL values will be excluded.

Example

Declare @YourTable Table ([ID] varchar(50),[Name] varchar(50))
Insert Into @YourTable Values 
 (1,'asd')
,(2,'xyz')

Select stuff(
        replace(
         replace(
          replace(
           replace(
            (Select * from @YourTable for XML RAW)
           ,'<row ',',')
          ,'="',':')
         ,'" ',',')
        ,'"/>','')
       ,1,1,'')

Returns

(No column name)
ID:1,Name:asd,ID:2,Name:xyz

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Use the information schema views. They contain all the information you need to generate your dynamic sql. The rest is just simple SQL and patience.

Upvotes: 0

Related Questions