0

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
KousiK
  • 825
  • 7
  • 17
  • 39
  • Short answer: It is impossible. Slightly longer: what about distinct values in distinct rows of you "other" column? How this should be handled? – Andrey Korneyev Dec 22 '15 at 08:46
  • take look at this question http://dba.stackexchange.com/questions/59062/how-can-i-dynamically-alias-columns – Shaminder Singh Dec 22 '15 at 08:50
  • I have a table with a date field which is start_date and 31 fixed field like dt1,dt2,dt3... I want dt1 column should display as dd value of the start_date column. – KousiK Dec 22 '15 at 08:56
  • show some sample data. – Avi Dec 22 '15 at 08:57
  • please have a look at the edited qsn – KousiK Dec 22 '15 at 09:03
  • If possible, apply such *formatting* concerns at a presentation layer (application code dealing with presenting data to the user, or some form of reporting tool). Otherwise, you'll have to go to dynamic SQL because any particular query in SQL will always produce a result set with a fixed "shape" - the number of columns, their *names* and their types. – Damien_The_Unbeliever Dec 22 '15 at 09:09
  • I have already done this in presentation layer but I have to do this in a query. How can this be possible using dynamic sql? @Damien_The_Unbeliever – KousiK Dec 22 '15 at 09:14

3 Answers3

2

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>
Sascha
  • 1,210
  • 1
  • 17
  • 33
  • I have a table with a date field which is start_date and 31 fixed field like dt1,dt2,dt3... I want dt1 column should display as dd value of the start_date column – KousiK Dec 22 '15 at 08:57
1

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

Quantumplate
  • 1,104
  • 8
  • 15
0

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

danpeall
  • 21
  • 3