CODEWITHSUNDEEP
sql-server
KousiK
KousiK

Reputation: 825

Generate column name dynamically in sql server

Please look at the below query..

select name as [Employee Name] from table name.

I want to generate [Employee Name] dynamically based on other column value.

Here is the sample table

s_dt    dt01    dt02    dt03
2015-10-26

I want dt01 value to display as column name 26 and dt02 column value will be 26+1=27

Upvotes: 0

Views: 15846

Answers (3)

Quantumplate
Quantumplate

Reputation: 1104

You would need to dynamically build the SQL as a string then execute it. Something like this...

DECLARE @s_dt INT
DECLARE @query NVARCHAR(MAX)

SET @s_dt = (SELECT DATEPART(dd, s_dt) FROM TableName WHERE 1 = 1) 

SET @query = 'SELECT s_dt'
  + ', NULL as dt' + RIGHT('0' + CAST(@s_dt as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 1) as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 2) as VARCHAR), 2)
  + ', NULL as dt' + RIGHT('0' + CAST((@s_dt + 3) as VARCHAR), 2)
  + ' FROM TableName WHERE 1 = 1)

EXECUTE(@query)

You will need to replace WHERE 1 = 1 in two places above to select your data, also change TableName to the name of your table and it currently puts NULL as the dynamic column data, you probably want something else there.

To explain what it is doing:

SET @s_dt is selecting the date value from your table and returning only the day part as an INT.

SET @query is dynamically building your SELECT statement based on the day part (@s_dt).

Each line is taking @s_dt, adding 0, 1, 2, 3 etc, casting as VARCHAR, adding '0' to the left (so that it is at least 2 chars in length) then taking the right two chars (the '0' and RIGHT operation just ensure anything under 10 have a leading '0').

Upvotes: 1

danpeall
danpeall

Reputation: 21

It is possible to do this using dynamic SQL, however I would also consider looking at the pivot operators to see if they can achieve what you are after a lot more efficiently.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 0

Sascha
Sascha

Reputation: 1218

I'm not sure if I understood you correctly. If I'am going into the wrong direction, please add comments to your question to make it more precise.

If you really want to create columns per sql you could try a variation of this script:

DECLARE @name NVARCHAR(MAX) = 'somename'

DECLARE @sql NVARCHAR(MAX) = 'ALTER TABLE aps.tbl_Fabrikkalender ADD '+@name+' nvarchar(10) NULL'

EXEC sys.sp_executesql @sql;

To retrieve the column name from another query insert the following between the above declares and fill the placeholders as needed:

SELECT @name = <some colum> FROM <some table> WHERE <some condition>

Upvotes: 2

Related Questions