0

Need SQL logic

I have table with Column name EID, Emp_name, 1,2,3,4,5,6,7....till 31 (Column 1 to 31 are calendar dates) now i m trying to fetch only 3 column EID, EMP_name and date which is equals to sys date.

Example

Todays SYS_date is 2nd-Jan-2019

and i need column with value = 2 like this...

EID    Emp_name        2  |
123    James SCOTT     P  |  
133    Mark M          A  |
133    Mark Man        P  |
jarlh
  • 42,561
  • 8
  • 45
  • 63
J.James
  • 11
  • 5
  • 2
    2 solutions: [UNPIVOT statement](https://stackoverflow.com/questions/19055902/unpivot-with-column-name), or [Dynamic SQL statement](https://stackoverflow.com/questions/2727387/sql-server-variable-columns-name). – Thomas G Jan 02 '19 at 11:28
  • anything will do, but prefer both solutions – J.James Jan 02 '19 at 11:30
  • @ThomasG it did not solved my problem :( – J.James Jan 02 '19 at 11:42

2 Answers2

0

Try like this: 

declare @sql nvarchar(max)
set @sql = 'select EID, Emp_name, [' + convert(nvarchar(2),day(getdate())) + '] as d from tableName '

exec(@sql)
claud.io
  • 1,523
  • 3
  • 15
  • 30
  • i m getting error Conversion failed when converting the varchar value 'select EID, ' to data type int. – J.James Jan 02 '19 at 11:38
  • i tried this but i m getting values 2,2,2,2,2,2 in the rows for column d but i want associated values from column [2] and in rows P, A,P, WO, – J.James Jan 02 '19 at 12:16
  • You should surround the "day" column with square brackets, because it is a number, which is invalid column name and will select the number itself, not the contents of the column with this name. I.e. `set @sql = 'select EID, Emp_name, [' + convert(nvarchar(2),day(getdate())) + '] as d from tableName'` – Andrey Nikolov Jan 02 '19 at 12:17
  • @AndreyNikolov how i can use this dynamic sql query in php ? i mean i m using $query = "select * from mytable "; $result = sqlsrv_query ($conn,$query); – J.James Jan 02 '19 at 13:53
  • @J.James Please, do not post questions as comments. If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. This will help getting an answer. Otherwise, if you will execute this from PHP (or any other programming language), why not just create your query text in your program and execute it directly? – Andrey Nikolov Jan 02 '19 at 15:23
  • @AndreyNikolov i used this script to create view but if date changed after 12.01 AM the column remain same ! can u figure it out why ? lets say todays date is 7 but the column value is for [3], the day i created view, plz help – J.James Jan 07 '19 at 11:21
  • The view is created from specific select statement. It already has list of columns defined. It wont change automatically on the next day. – Andrey Nikolov Jan 07 '19 at 12:06
0

One method to approach this is to unpivot the data. Here is one method:

select t.eid, t.emp_name, v.n
from t cross apply
     (values ([1]), ([2]), ([3]), . . ., ([4])
     ) v(n)
where v.n = day(getdate());

I will caution that the column name is a fixed name, not 2. SQL queries have fixed column names. You would need to use dynamic sql to get a variable column name.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786