Question: Here is the sample
![See Sample](https://i.stack.imgur.com/VUGo3.png)

How to get this data based on day name from my table?

Answer: 
You should really fix your design. Each doctor/day tuple should be stored as a separate *row* rather than in columns.

For your current data structure, I would recommend unpivoting, then filtering:

```
select t.doctorname, v.*
from mytable t
cross apply (values 
    ('Sunday', sunday)
    ('Monday', monday)
    ...
) v(dy, val)
where v.dy = datename(weekday, getdate())

```
```
declare @t table
(
Doctor_Name varchar(20),
Sunday bit, Monday bit, Tuesday bit, Wednesday bit, Thursday bit, Friday bit, Saturday bit
);

insert into @t values
('dr.Sunday', 1, 0, 0, 0, 0, 0, 0),
('dr.Monday', 0, 1, 0, 0, 0, 0, 0),
('dr.Tuesday', 0, 0, 1, 0, 0, 0, 0),
('dr.Friday', 0, 0, 0, 0, 0, 1, 0), 
('dr.Tuesday&Saturday', 0, 0, 1, 0, 0, 0, 1);

select *
from @t
where choose(datepart(weekday, dateadd(day, @@datefirst, getdate())), Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday) = 1;

```